Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QCY3h64865 for ; Wed, 26 Sep 2001 08:34:03 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8QCR1A06057 for pgsql-sql@postgresql.org; Wed, 26 Sep 2001 08:27:01 -0400 (EDT) (envelope-from news) From: "Thurstan R. McDougle" 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 13:26:52 +0100 Message-ID: <3BB1C98C.D45484C3@my-deja.com> References: <89c01029.0109251901.3dcb5d77@posting.google.com> X-NNTP-Posting-Host: no-dns-yet.demon.co.uk:193.195.66.176 X-Complaints-To: abuse@demon.net X-Mailer: Mozilla 4.77 [en] (Win98; U) X-Accept-Language: en,pdf MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 69 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/224 X-Sequence-Number: 4906 How about:- SELECT id FROM (a LEFT JOIN b WHERE a.flag=b.flag) GROUP BY id HAVING ((COUNT(*)=COUNT(b.flag)) AND (COUNT(*)=(SELECT COUNT(*) FROM b AS b_cnt))); This relys on COUNT(field) not counting NULLs, and that NULL is what the LEFT JOIN returns for an absent b.flag:- ID a.FLAG b.FLAG ----- ------ ------ 1 1 1 2 1 1 2 2 2 3 1 1 3 2 2 3 3 NULL 4 1 1 4 3 NULL N.B In your test table you missed the case of having some, but not all of b's flags and one or more others... ID FLAG ----- ----- 4 1 4 3 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. -- This is the identity that I use for NewsGroups. Email to this will just sit there. If you wish to email me replace the domain with knightpiesold . co . uk (no spaces).