Counting rows from two tables in one query - Mailing list pgsql-sql

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