MySQL number does not return 0 if record is not found

Question

Although I researched this topic and came across several solutions, such as using JOIN LEFT or subqueries, I still can not get the result that I want, since I am not very strong in mySQL. I work more as a web designer, trying to use simple php on my website for a school project.

I am trying to create a web application similar to a blog. I wanted to calculate how many comments there are for the message and display the number for my users, but if there are no comments for this line, my query will not return anything, not 0.

This is my request below:

SELECT post.post_id, COUNT(comment) FROM `comment`, post WHERE `comment`.post_id = post.post_id GROUP BY post.post_id 

Result:

 Record | post_id | COUNT(comment) 1 | 12 | 2 2 | 13 | 1 3 | 15 | 1 4 | 16 | 1 

As you can see, post_id 14 has no comments, so my query returns nothing. What should I do to make the result like this?

 Record | post_id | COUNT(comment) 1 | 12 | 2 2 | 13 | 1 3 | 14 | 0 4 | 15 | 1 5 | 16 | 1 

Also, it would be nice if you guys gave me links or links to understand the concept of the solution, as I want to know more about php :)

Answer 1

So, when you do this (this is what you do, reformulated for JOIN):

 SELECT post.post_id, COUNT(comment) FROM `comment` INNER JOIN post ON `comment`.post_id = post.post_id GROUP BY post.post_id; 

You only collect posts that have at least one link in the comment.

If you change the JOIN type to LEFT join , as follows:

 SELECT post.post_id, COUNT(comment) FROM `comment` LEFT JOIN post ON `comment`.post_id = post.post_id GROUP BY post.post_id; 

Then the rows from the column are all there, and NULL values ​​are inserted for comment columns if there are no comments associated with this row (this is the left join). Therefore, if the comment is a column from the table comment, it will be present for each row of the column table, but with a NULL value, after the group in the post_id column, the subset of comments associated with this message contains only 1 NULL, the counter should return 0.

 select count(NULL); 

returns 0.

Now you can use a subquery, but this is a really bad idea, subqueries are usually executed instead of LEFT JOINS, usually this is an error , sometimes it is not, but in fact it is often an error. When you make left join indexes, they are used to compare the key values ​​of two tables (the ON clause) and construct one final “temporary” result of the rows, mix the values ​​from both tables (and then, or, possibly, at the same time, apply filters from other parts of your requests). When you use a subquery, for each row of the first table, a new query is launched to get the results from the second table (not always, but this is another problem), the cost will be significantly higher for the database engine.

Answer 2

Request a column and run a subquery to count in the comment request.

 SELECT post.post_id, (SELECT COUNT(comment) FROM `comment` WHERE `comment`.post_id = post.post_id) as comments FROM post 

This can be very slow with batches or lines, so add a limit with a pager when you get to this point.