Re: Need help with a complicate sql select request

Thorsten Knopel wrote: > Sorry corrected value in Table B > > Thorsten Knopel wrote: > >> hello , i need some help for a complicate sql search, hope someone can >> help me >> >> >> Problem: >> >> Table A: mega_grp, grp >> ---------------------- >> >> e.g. mg1, grp1 >> mg1, grp2 >> mg2, grp3 >> ----------------------- >> >> Table B: user, grp >> ---------------------- >> e.g. usr1, grp1 >> usr1, grp2 >> usr2, grp1 >> usr2, grp3 >> ---------------------- >> >> >> What we want is to find all user which are in mega_group "mg1". That >> mean all user which are in "grp1" and "grp2". Not only in one but in >> both. In the example it would be only "usr1" because "usr2" is not in >> "grp2" Note that 'user' is a poor choice for a column name since it is also a built-in variable returning the user's login name. So it must be qualified in queries with the tablename: select mega_grp, b.user, count(*) from tablea a join tableb b on a.mega_grp = 'mg1' and a.grp = b.grp group by mega_grp, user having count(*) > 1; Just leave off the "a.mega_grp = 'mg1'" filter to find all users in any mega-grp. This will work for mega-grps defined with 2 or more grps/ Art S. Kagel >> I tried it with a subselect and an IN Operation but then i get all >> user which are in one of the groups but not in both. >> >> >> Thanxs for your help >> >> Thorsten