Search code examples
sqlpostgresqlleft-joincorrelated-subquery

Joining three tables with aggregation

I have the following items table:

items:
id    pr1  pr2  pr3
-------------------
1     11   22   tt
...

and two tables associated with the items:

comments:
item_id  text
-------------
1       "cool"
1       "very good"
...

tags:
item_id  tag
-------------
1        "life"
1        "drug"
...

Now I want to get a table with columns item_id, pr1, pr2, count(comments), count(tags) with a condition WHERE pr3 = zz. What is the best way to get it? I can do this by creating additional tables, but I was wondering if there is a way achieve this by using only a single SQL statement. I'm using Postgres 9.3.

Solution

  • The easiest way is certainly to get the counts in the select clause:

    select 
  id, 
  pr1, 
  pr2,
  (select count(*) from comments where item_id = items.id) as comment_count,
  (select count(*) from tags where item_id = items.id) as tag_count
from items;