CODEWITHSUNDEEP
mysqlsql
user1646859
user1646859

Reputation:

Getting higher results than expected with 2 joins

This is the query I have:

SELECT  p.id, COUNT(c.id) AS comments, COUNT(a.id) AS answers
FROM posts_tbl AS p
LEFT JOIN post_reply_tbl AS c ON c.post_id = p.id AND c.type IN (1,3)
LEFT JOIN post_reply_tbl AS a ON a.post_id = p.id AND a.type = 2
GROUP BY p.id

What I am supposed to get as results is:

ID  Answers Comments
1   3       4
2   1       1
3   0       1

But instead I am getting:

ID  Answers Comments
1   12      12
2   1       1
3   0       1

When I use just 1 of the LEFT JOINs no matter which one it gives me the right amount for answers/comments (depending on which LEFT JOIN I use). But when I use them both it gives me the second result.

Sample result data with when using *:

id  username    date                subject                     year    brand   model   type    bodywork                    text                                                    image       chassis         id  post_id answer_id   username        text    date                type    id  post_id answer_id   username        text            date                type
1   Sinan Samet 2013-02-18 20:18:19 Dit is een test onderwerp   2006    Audi    A4  TFSI 2.0    TFSI 2.0 QUATTRO - 200pk    Lorem ipsum dolor sit amet consectetur adipiscing ...   test.jpg    345215453213425 5   1       NULL        Sinan Samet     Test    2013-02-26 09:19:22 1       1   1       NULL        Jan Willem      Bla             2013-02-26 14:14:17 2
1   Sinan Samet 2013-02-18 20:18:19 Dit is een test onderwerp   2006    Audi    A4  TFSI 2.0    TFSI 2.0 QUATTRO - 200pk    Lorem ipsum dolor sit amet consectetur adipiscing ...   test.jpg    345215453213425 5   1       NULL        Sinan Samet     Test    2013-02-26 09:19:22 1       2   1       NULL        Peter           Nee toch niet   2013-02-26 15:12:26 2
1   Sinan Samet 2013-02-18 20:18:19 Dit is een test onderwerp   2006    Audi    A4  TFSI 2.0    TFSI 2.0 QUATTRO - 200pk    Lorem ipsum dolor sit amet consectetur adipiscing ...   test.jpg    345215453213425 5   1       NULL        Sinan Samet     Test    2013-02-26 09:19:22 1       3   1       NULL        Homer Simpson   Doh!            2013-02-26 14:33:18 2
1   Sinan Samet 2013-02-18 20:18:19 Dit is een test onderwerp   2006    Audi    A4  TFSI 2.0    TFSI 2.0 QUATTRO - 200pk    Lorem ipsum dolor sit amet consectetur adipiscing ...   test.jpg    345215453213425 6   1       NULL        Bart Simpson    Yo      2013-02-26 08:38:37 1       1   1       NULL        Jan Willem      Bla             2013-02-26 14:14:17 2

Upvotes: 1

Views: 64

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

select
    p.id,
    COALESCE(c.comments,0) AS comments,
    COALESCE(a.answers, 0) AS answers
from
    posts_tbl as p
    left join (
        select post_id, count(*) as comments
        from post_reply_tbl
        where type in (1,3)
        group by post_id
    ) as c on p.id = c.post_id
    left join (
        select post_id, count(*) as answers
        from post_reply_tbl
        where type = 2
        group by post_id
    ) as a on p.id = a.post_id
order by id ;

Upvotes: 4

Related Questions