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 | 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?
thanks,
stefan
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
