pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedA simple join question that may stump you
4+ messages / 4 participants
[nested] [flat]
* A simple join question that may stump you
@ 2001-09-26 03:01 Ross Smith <nospam4@pobox.com>
2001-09-26 08:11 ` Re: A simple join question that may stump you A. Prins <prinsarian@zonnet.nl>
2001-09-26 12:26 ` Re: A simple join question that may stump you Thurstan R. McDougle <trmcdougle@my-deja.com>
2001-09-27 15:32 ` Re: A simple join question that may stump you Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 4+ messages in thread
From: nospam4@pobox.com @ 2001-09-26 03:01 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A simple join question that may stump you
2001-09-26 03:01 A simple join question that may stump you Ross Smith <nospam4@pobox.com>
@ 2001-09-26 08:11 ` A. Prins <prinsarian@zonnet.nl>
2 siblings, 0 replies; 4+ messages in thread
From: prinsarian@zonnet.nl @ 2001-09-26 08:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A simple join question that may stump you
2001-09-26 03:01 A simple join question that may stump you Ross Smith <nospam4@pobox.com>
@ 2001-09-26 12:26 ` Thurstan R. McDougle <trmcdougle@my-deja.com>
2 siblings, 0 replies; 4+ messages in thread
From: Thurstan R. McDougle @ 2001-09-26 12:26 UTC (permalink / raw)
To: pgsql-sql
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).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: A simple join question that may stump you
2001-09-26 03:01 A simple join question that may stump you Ross Smith <nospam4@pobox.com>
@ 2001-09-27 15:32 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-09-27 15:32 UTC (permalink / raw)
To: nospam4@pobox.com; pgsql-sql
Ross,
> 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
Try looking at the INTERSECT and EXCEPT join types. Thus, in pseudo-sql
you'd need:
SELECT A.ID
WHERE Count A JOIN B = Count B
AND Count A EXCEPT B = 0
Assuming that all rows in A are unique.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-09-27 15:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-26 03:01 A simple join question that may stump you Ross Smith <nospam4@pobox.com>
2001-09-26 08:11 ` A. Prins <prinsarian@zonnet.nl>
2001-09-26 12:26 ` Thurstan R. McDougle <trmcdougle@my-deja.com>
2001-09-27 15:32 ` Josh Berkus <josh@agliodbs.com>
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