Re: [GENERAL] 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, [EMAIL PROTECTED] (Michael Glaesemann) wrote: On Jun 21, 2007, at 11:57 , Josh Tolley wrote: On 6/21/07, danmcb [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] finding items with 0 rels for a 0 to many relationship
On 6/21/07, danmcb [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] finding items with 0 rels for a 0 to many relationship
On Jun 21, 2007, at 11:57 , Josh Tolley wrote: On 6/21/07, danmcb [EMAIL PROTECTED] 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
