From: Thurstan R. McDougle <trmcdougle@my-deja.com>
To: pgsql-sql@postgresql.org
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> (raw)
References: <89c01029.0109251901.3dcb5d77@posting.google.com>
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).
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: trmcdougle@my-deja.com
Subject: Re: A simple join question that may stump you
In-Reply-To: <3BB1C98C.D45484C3@my-deja.com>
* 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