How can I add multiple tables via LEFT JOIN and count the rows of each table?

Question

I am counting the results of a table I LEFT JOINED:

SELECT p.*,COUNT(po.name) AS posts
    FROM projects p
    left join posts po on p.name = po.name
    group by p.id

http://sqlfiddle.com/#!9/3e9d4b/4

But now I want to add another table via LEFT JOIN and count it also:

SELECT p.*,COUNT(po.name) AS posts,
    COUNT(ta.name) AS tasks
    FROM projects p
    left join posts po on p.name = po.name
    left join tasks ta on p.name = ta.name
    group by p.id

http://sqlfiddle.com/#!9/ee068/2

But now the counting is wrong. For cat I have only 2 posts and 3 tasks. Where is the number 6 coming from?

The number 6 is probably coming from that you are double counting tasks and/or posts. — Tim Biegeleisen, Nov 13 '17 at 13:52
Hi. Please read & act on [mcve]. You don't even say what you want--"add another table via LEFT JOIN and count it" does not tell us. Read the definition of left join. Look at the output before you do the second left join. That is where the 6 is coming from. You actually want (what can be expressed as) a join of two separate left joins. This is a faq that you would find if, as you always should, you googled adequately--google my comments re googling many phrasings. — philipxy, Nov 13 '17 at 14:18
Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Nov 13 '17 at 14:19

score 2 · Accepted Answer · answered Nov 13 '17 at 13:43

2

Left joins are not the right tool for this. You should use subselects:

SELECT p.*,
  (SELECT COUNT(*) FROM posts po WHERE p.name = po.name) AS posts,
  (SELECT COUNT(*) FROM tasks ta WHERE p.name = ta.name) AS tasks
FROM projects p

answered Nov 13 '17 at 13:43

mrks

8,033
1
33
62

Left joins are the right tool for this I think, but your answer is interesting nonetheless. – Tim Biegeleisen Nov 13 '17 at 13:53
Well... I am not saying that you can't use them, but they are not the best tool. – mrks Nov 13 '17 at 13:54

score 1 · Answer 2 · answered Nov 13 '17 at 13:47

You can still use joins, but instead of a single top level aggregation, you can aggregate each of the two tables in separate subqueries:

SELECT
    p.name,
    COALESCE(t1.posts_cnt, 0) AS posts_cnt,
    COALESCE(t2.tasks_cnt, 0) AS tasks_cnt
FROM projects p
LEFT JOIN
(
    SELECT name, COUNT(*) AS posts_cnt
    FROM posts
    GROUP BY name
) t1
    ON p.name = t1.name
LEFT JOIN
(
    SELECT name, COUNT(*) AS tasks_cnt
    FROM tasks
    GROUP BY name
) t2
    ON p.name = t2.name

Your current queries have problems, because you are aggregating by id but selecting other columns. From what I see, you want to be aggregating in all three tables using the name column.

This approach should outperform an approach using correlated subqueries.

How can I add multiple tables via LEFT JOIN and count the rows of each table?

2 Answers2