Re: Need help with a complicate sql select request

From: Art S. Kagel <kagel_at_bloomberg.net>
Date: Fri, 07 Jul 2006 10:34:31 -0400
Message-ID: <44AE70F7.7000504@bloomberg.net>

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
Received on Fri Jul 07 2006 - 09:34:31 CDT