Обсуждение: finding items with 0 rels for a 0 to many relationship
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)? Thanks Daniel
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. On 21 Jun, 19:13, g...@seespotcode.net (Michael Glaesemann) wrote: > On Jun 21, 2007, at 11:57 , Josh Tolley wrote: > > > > > On 6/21/07, danmcb <danielmcbrea...@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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
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...@seespotcode.net (Michael Glaesemann) wrote: > On Jun 21, 2007, at 11:57 , Josh Tolley wrote: > > > > > On 6/21/07, danmcb <danielmcbrea...@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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster