Counting rows from two tables in one query

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

Responses

Browse pgsql-sql by date

  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