CODEWITHSUNDEEP
mysql
user726049
user726049

Reputation:

MySQL LEFT JOIN not returning all rows from the left table

I have been working on a blog system recently, and I ran into an issue. I have two tables, one for blog posts, and another for comments on those posts. This is the query that I am currently using for retrieving of all post information and also the number of comments:

SELECT bp.*, COUNT(bpc.id) AS post_comments
FROM blog_posts AS bp
LEFT JOIN blog_post_comments AS bpc ON bpc.post_id = bp.id
LIMIT 0, 10

Now, as I stated in the title, this only returns me data about one post, although it should return 10 posts. Why is this so?

Thanks!

Upvotes: 1

Views: 1397

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270607

You are missing the GROUP BY clause, and since MySQL is lenient about aggregate functions and columns in the SELECT not appearing in the group by, it collapses down to one row showing the total count, but mixed column values from other columns.

SELECT bp.*, COUNT(bpc.id) AS post_comments
FROM blog_posts AS bp
LEFT JOIN blog_post_comments AS bpc ON bpc.post_id = bp.id
GROUP BY bp.id
LIMIT 0, 10

The more portable way to do this for RDBMS other than MySQL would be to join against a subquery that returns only the post_id and the number of posts per id, allowing the rest of the columns from blog_posts to be selected without appearing in the GROUP BY.

SELECT
  bp.*,
  bcount.counts
FROM 
  blog_posts AS bp
  LEFT JOIN (
     SELECT post_id, COUNT(*) AS counts 
     FROM blog_post_comments 
     GROUP BY post_id
  ) bcounts ON bp.id = bcounts.post_id

Upvotes: 4

NSF
NSF

Reputation: 2549

Since you use COUNT, it will consider all posts as the same. Try this to see if it works:

SELECT bp.*, COUNT(bpc.id) AS post_comments
FROM blog_posts AS bp
LEFT JOIN blog_post_comments AS bpc ON bpc.post_id = bp.id 
GROUP BY bp.id
LIMIT 0, 10

I'm not sure if that's the problem. Just guessing. :)

Upvotes: 2

Sergey Eremin
Sergey Eremin

Reputation: 11080

You get one record because COUNT() is an aggregate function. You should add a GROUP BY clause and COUNT() will work as you wanted.

Upvotes: 1

Related Questions