Counting rows from two tables in one query
От | Stefan Weiss |
---|---|
Тема | Counting rows from two tables in one query |
Дата | |
Msg-id | 1928683.cthc5y6rJJ@weyoun.foo.at обсуждение исходный текст |
Ответы |
Re: Counting rows from two tables in one query
Re: Counting rows from two tables in one query |
Список | 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
В списке pgsql-sql по дате отправления: