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...@xxxxxxxxxxxxxxx (Michael Glaesemann) wrote: > On Jun 21, 2007, at 11:57 , Josh Tolley wrote: > > > > > On 6/21/07, danmcb <danielmcbrea...@xxxxxxxxx> 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
