Search code examples
mysqlzero

Mysql - show all results, including 0 results

so I have the following tables:

Article

  • article_id

Comment

  • comment_id
  • article_id

What I want to do is search for all articles despite having or not comments and show the article id and how many comments it has.

Imagine that I have two articles and just the first one has comments. I can't get to make a query that shows me both of them and their comments number.

EDIT 1:

I made the following query after reading the responses and I'm almost there! There is only a problem. Instead of getting 0 when a article has no comments, I get 1.

SELECT *,COUNT(a.article_id) FROM article as a LEFT JOIN comment as c ON a.article_id = c.article_id GROUP BY a.article_id;

EDIT 2:

A simple mistake. I changed "COUNT(a.article_id)" to "COUNT(C.article_id)". So obvious! :) Thanks for the help people ;)

SELECT *,COUNT(c.article_id) FROM article as a LEFT JOIN comment as c ON a.article_id = c.article_id GROUP BY a.article_id;

Solution

  • Try this:

    SELECT * FROM Article as a INNER JOIN Comment as c ON a.article_id = c.article_id;