Re: Counting rows from two tables in one query

On Wed, Apr 07, 2004 at 10:06:20 +0000,
Stefan Weiss <spaceman-75e1f-20040422(at)ausgehaucht(dot)sensenmann(dot)at> 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.