Re: finding items with 0 rels for a 0 to many relationship

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.

On 21 Jun, 19:13, g(dot)(dot)(dot)(at)seespotcode(dot)net (Michael Glaesemann) wrote:
> On Jun 21, 2007, at 11:57 , Josh Tolley wrote:
>
>
>
> > On 6/21/07, danmcb <danielmcbrea(dot)(dot)(dot)(at)gmail(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster