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