What is the correct mysql query to perform a join and a count?

I have two table, one holds information about 开发者_如何转开发images and the other contains information about comments agains the images.

I need to have a query that shows the count of comments against an image in one query. I have tried the below:

SELECT l.img_id, COUNT(c.comment_id) AS comment_count FROM tbl_images l, tbl_image_comments c WHERE l.img_id = c.img_id;

but this doesnt report correctly as it just counts all of the comments table.

Can anyone help please :)

What you are doing here is making a CROSS JOIN of the images and comments tables: each row from images gets "paired" with all the rows from comments and vice versa (this explains the comment count), and then you filter on the resulting dataset with the WHERE clause.

What you should be doing is a LEFT OUTER JOIN, which "pairs" each row from images only with those rows from comments which fulfil a specific condition:

SELECT l.img_id, COUNT(c.comment_id) AS comment_count
FROM tbl_images l
LEFT JOIN tbl_image_comments c ON l.img_id = c.img_id

you need to specify a grouping for the count to work as you expect.

try adding

GROUP BY l.img_id

on the end of the query

as the option:

SELECT 
  l.img_id, 
  (select COUNT(*) from  tbl_image_comments c l.img_id = c.img_id) AS comment_count 
FROM 
  tbl_images l;