Thread: Counting rows from two tables in one query

Hi.

I have a (simplified) table layout like this:
       +---------+                          +---------+       | sub_a   |         +------+         | sub_b   |
+---------+        | main |         +---------+       | id      |         +------+         | id      |       | main_id
| ---->  | id   |  <----  | main_id |       | ...     |         | ...  |         | ...     |       +---------+
+------+        +---------+
 

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       | 12       | 12      | 13       | 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?

thanks,
stefan

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

                
 
> What I am trying to get is a list that shows how many records from
> 'sub_a' and 'sub_b' are referencing 'main':
                                                
 
SELECT id, (SELECT COUNT(*) FROM sub_a WHERE sub_a.main_id=main.id), (SELECT COUNT(*) FROM sub_b WHERE
sub_b.main_id=main.id)
FROM main ORDER BY id;
                         
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404092128
-----BEGIN PGP SIGNATURE-----
iD8DBQFAd03pvJuQZxSWSsgRAuDuAJ0elm2bPjgC1bGPHnrotzXrPKCt4ACdFytf
BglMm6IouFFZt1c19zST5ac=
=7DbY
-----END PGP SIGNATURE-----

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