On Wed, Apr 07, 2004 at 10:06:20 +0000, Stefan Weiss <[EMAIL PROTECTED]> wrote: > Hi. > > What I am trying to get is a list that shows how many records from > 'sub_a' and 'sub_b' are referencing 'main': > > main_id | count_a | count_b > ---------+---------+--------- > 1 | 2 | 1 > 2 | 12 | 1 > 3 | 7 | 3 > [......] > > This query obviously does not do what I need, it gives me the product > of count_a and count_b in both columns instead: > > select main.id as main_id, > count(sub_a.*) as count_a, > count(sub_b.*) as count_b > from main, > sub_a, > sub_b > where sub_a.main_id = main.id > and sub_b.main_id = main.id > group by main.id > having count(sub_a.*) > 0 > and count(sub_b.*) > 0 > ; > > Is it possible to get a list like the one above with a single query?
You want something along the lines of: SELECT main.id, a.acnt, b.bcnt FROM main LEFT JOIN (SELECT main_id, count(*) AS acnt FROM sub_a GROUP BY main_id) AS a ON (main.id = a.main_id) LEFT JOIN (SELECT main_id, count(*) AS acnt FROM sub_b GROUP BY main_id) AS b ON (main.id = b.main_id) ; I haven't tested the above, so there might be some minor problem with it. Also depending on how you want to handle cases where there are no references to an id in main in either sub_a and/or sub_b, you may need to modify the query. The way it is now, you will get NULLs in those cases instead of zero. ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])