From: prinsarian@zonnet.nl
To: pgsql-sql@postgresql.org
Subject: Re: A simple join question that may stump you
Date: Wed, 26 Sep 2001 08:11:57 GMT
Message-ID: <3bb18d96.7263417@news.amc.uva.nl> (raw)
References: <89c01029.0109251901.3dcb5d77@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: prinsarian@zonnet.nl
Subject: Re: A simple join question that may stump you
In-Reply-To: <3bb18d96.7263417@news.amc.uva.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox