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