Обсуждение: A simple join question that may stump you
OK, I have 2 tables, table A: ID FLAG ----- ----- 1 1 2 1 2 2 3 1 3 2 3 3 and table B: FLAG ----- 1 2 I want to find all id's from table A that have every flag in table B but no extra flags. So, I'd end up with: ID ----- 2 As id 2 has both flag 1 and flag 2, id 1 doesn't have flag 2, and id 3 has flag 3. I know it can be done, 'cause I've done it in the past, but I've spent hours on this to no avail. Surfing the net proved fruitless as well. Any help would be greatly appreciated.
This is one way that comes up: select id from ( select distinct a.id AS id , b.flag AS flag from A, B where a.flag = b.flag ) a_distinct where id not in (select id from a where flag not in (select flag from b)) group by id having count(*) = (select count(*) from b) ; Arian. On 25 Sep 2001 20:01:06 -0700, nospam4@pobox.com (Ross Smith) wrote: >OK, I have 2 tables, table A: > > ID FLAG >----- ----- > 1 1 > 2 1 > 2 2 > 3 1 > 3 2 > 3 3 > >and table B: > > FLAG >----- > 1 > 2 > >I want to find all id's from table A that have every flag in table B >but no extra flags. So, I'd end up with: > > ID >----- > 2 > >As id 2 has both flag 1 and flag 2, id 1 doesn't have flag 2, and id 3 >has flag 3. > >I know it can be done, 'cause I've done it in the past, but I've spent >hours on this to no avail. Surfing the net proved fruitless as well. > >Any help would be greatly appreciated. Arian Prins / Rock Resort --U-N-L-E-A-S-H-E-D-- (keyboards/production/songwriting) listen at: http://www.mp3.com/RockResort
Ross, > ID FLAG > ----- ----- > 1 1 > 2 1 > 2 2 > 3 1 > 3 2 > 3 3 > > and table B: > > FLAG > ----- > 1 > 2 > > I want to find all id's from table A that have every flag in table B > but no extra flags. So, I'd end up with: > > ID > ----- > 2 Try looking at the INTERSECT and EXCEPT join types. Thus, in pseudo-sql you'd need: SELECT A.ID WHERE Count A JOIN B = Count B AND Count A EXCEPT B = 0 Assuming that all rows in A are unique. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco