Message-Id: <1928683.cthc5y6rJJ@weyoun.foo.at>
From: Stefan Weiss <spaceman-75e1f-20040422@ausgehaucht.sensenmann.at>
Subject: Counting rows from two tables in one query
Lines: 42
Organization: Foo Orbital Operations
User-Agent: KNode/0.7.1
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
Date: Wed, 07 Apr 2004 10:06:20 GMT
To: pgsql-sql@postgresql.org

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