CODEWITHSUNDEEP
mysqljoin
mrmoment
mrmoment

Reputation: 757

MySQL query comment number of articles together with article details

I have three tables, Users, Articles and Comments, as below:

Users:

id | username

Articles:

id | user_id | title | content | time

Comments:

id | article_id | content | time

user_id is the article writer's user id in Users; article_id is the commenting article's id in Articles.

Now I want to list articles, showing their writer's username, title, content, time, and number of comments. The username is from Users, number of comments should be from Comments by counting something, and the rest fields are from Articles.

How to write the query in a single statement?

I tried

SELECT a.*, count(c.id) AS NUM_COMMENTS, u.username
    FROM Commetns c
    JOIN Users u
    INNER JOIN Articles a
    WHERE a.id = c.article_id AND u.id=a.user_id
    GROUP BY a.id

But this only returns articles with comments. I want the articles without comments also listed.

Upvotes: 0

Views: 49

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You need left join to get the articles without comments 

select 
a.*,
count(c.id) AS NUM_COMMENTS,
u.username
from Articles a
JOIN Users u on  u.id=a.user_id
left join Commetns c on c.article_id = a.id
GROUP BY a.id

Upvotes: 1

Related Questions