finding items with 0 rels for a 0 to many relationship

On 6/21/07, danmcb <danielmcbrearty@gmail.com> wrote:

Hi

I have two tables, say A and B, that have a many-to-many
relationship, implemented in the usual way with a join table A_B.

How can I economically find all the rows in table A whose id's are not
in A_B at all (i.e. they have zero instances of B associated)?

Use a left join. For instance, say there are a.id and b.id columns,
which are the primary keys in A and B respectively. Also say A_B
contains columns aid and bid which reference a.id and b.id
respectively.

SELECT * FROM A LEFT JOIN A_B ON (A.ID = A_B.AID) WHERE A_B.BID IS NULL;

- Josh

On Jun 21, 2007, at 11:57 , Josh Tolley wrote:

On 6/21/07, danmcb <danielmcbrearty@gmail.com> wrote:

Hi

I have two tables, say A and B, that have a many-to-many
relationship, implemented in the usual way with a join table A_B.

How can I economically find all the rows in table A whose id's are
not
in A_B at all (i.e. they have zero instances of B associated)?

Use a left join. For instance, say there are a.id and b.id columns,
which are the primary keys in A and B respectively. Also say A_B
contains columns aid and bid which reference a.id and b.id
respectively.

SELECT * FROM A LEFT JOIN A_B ON (A.ID = A_B.AID) WHERE A_B.BID IS
NULL;

Alternatively you can use EXCEPT. Using Josh's schema:

SELECT id
FROM A
EXCEPT
SELECT aid
FROM A_B.

You'll want to check with EXPLAIN ANALYZE, but in general I suspect
the outer join is faster.

Michael Glaesemann
grzm seespotcode net

thanks both for this. I haven't got around to writing this part of the
code yet, but will do soon. I appreciate the pointers.