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



view thread (4+ messages)  latest in thread

Message-ID: <3BB1C98C.D45484C3@my-deja.com>
Permalink:  ../3BB1C98C.D45484C3@my-deja.com/
Also on:    postgresql.org/message-id/3BB1C98C.D45484C3@my-deja.com

 · 

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: 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