CODEWITHSUNDEEP
mysqljoincount
Mohammad Hossein Fattahizadeh
Mohammad Hossein Fattahizadeh

Reputation: 2711

sql multiple join and count

I have tables like this. but it seems MySQL doesn't count my second join currently. i want to know what i missed for process count of reports for my comment list.

enter image description here

and i want to have average of rates also count of reports

SELECT *, avg(rate.score), count(report.id) FROM `comment`
left join rate on (comment.id = rate.comment_id)
left join report on (comment.id = report.comment_id)
group by comment.id

id  text            id      comment_id  score   id      comment_id  type    avg(rate.score)     count(report.comment_id)
1   good article    1       1           2       1       1           1       4.0000              20
2   bad article     NULL    NULL        NULL    NULL    NULL        NULL    NULL                0

good article have 2 reports.

count(report.id) give me wrong value. what's my mistake?

Upvotes: 0

Views: 112

Answers (2)

Zagor23
Zagor23

Reputation: 1963

SELECT 
    *,
    avg(rate.score),
    (SELECT 
            count(report.comment_id)
        FROM
            report
        WHERE
            comment.id = report.comment_id) AS num_reports
FROM
    comment
        left join
    rate ON (comment.id = rate.comment_id)
group by comment.id

Here's the example:

http://sqlfiddle.com/#!2/cf313/15

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

You dont need *. Try this

SELECT comment.id, avg(rate.score), count(report.id) FROM `comment` 
left join rate on (comment.id = rate.comment_id) 
left join report on (comment.id = report.comment_id) 
group by comment.id

Upvotes: 0

Related Questions