From: Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
To: pgsql-sql@postgresql.org
Subject: Counting rows from two tables in one query
Date: Wed, 07 Apr 2004 10:06:20 GMT
Message-ID: <1928683.cthc5y6rJJ@weyoun.foo.at> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spaceman-75e1f-20040422@ausgehaucht.sensenmann.at
Subject: Re: Counting rows from two tables in one query
In-Reply-To: <1928683.cthc5y6rJJ@weyoun.foo.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox