From: | Stefan Weiss <spaceman-75e1f-20040422(at)ausgehaucht(dot)sensenmann(dot)at> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Counting rows from two tables in one query |
Date: | 2004-04-07 10:06:20 |
Message-ID: | 1928683.cthc5y6rJJ@weyoun.foo.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Cris Carampa | 2004-04-07 12:47:52 | setting a non-standard date format for the duration of a session |
Previous Message | Erik Thiele | 2004-04-07 09:42:12 | Re: Function To Log Changes |