pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (4+ messages)  latest in thread

Message-ID: <3bb18d96.7263417@news.amc.uva.nl>
Permalink:  ../3bb18d96.7263417@news.amc.uva.nl/
Also on:    postgresql.org/message-id/3bb18d96.7263417@news.amc.uva.nl

 · 

reply

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