Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8Q8Y2h08057 for ; Wed, 26 Sep 2001 04:34:02 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8Q8CX303484 for pgsql-sql@postgresql.org; Wed, 26 Sep 2001 04:12:33 -0400 (EDT) (envelope-from news) From: prinsarian@zonnet.nl (A. Prins) X-Newsgroups: mailing.database.mysql, comp.databases.postgresql.sql, microsoft.public.sqlserver.server Subject: Re: A simple join question that may stump you Date: Wed, 26 Sep 2001 08:11:57 GMT Organization: Academic Medical Center, The Netherlands Lines: 58 Message-ID: <3bb18d96.7263417@news.amc.uva.nl> References: <89c01029.0109251901.3dcb5d77@posting.google.com> X-Complaints-To: usenet@news.surfnet.nl X-Newsreader: Forte Free Agent 1.21/32.243 Cache-Post-Path: sphere.amc.uva.nl!unknown@amcnip46175.amc.uva.nl X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/) To: pgsql-sql@postgresql.org X-Archive-Number: 200109/248 X-Sequence-Number: 4930 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