Multiple mysql joins

Question

I have three tables that I need get information from, 1 table has the information in and the other two hold information that i need to count.

so the first tables structure is:

tbl_img
img_id
img_name

tbl_comments
comment_id
img_id
comment

tbl_vote
vote_id
logo_id

I want the results to have the count of comments and votes that relate to each logo.

I have a bit of the query which is for the count of comments, but have no idea for the syntax for the second join.

SELECT l.img_id, l.img_name, COUNT(c.comment_id) AS comment_count
FROM tbl_images as l
LEFT OUTER JOIN tbl_comments AS c USING (img_id);

Can anyone help?

Answer 1

how about this :

SELECT l.img_id, l.img_name, 
 (SELECT COUNT(*) FROM tbl_comments c WHERE i.img_id = c.img_id ) AS comment_count,
 (SELECT COUNT(*) FROM tbl_vote v WHERE i.img_id = v.img_id ) AS vote_count
FROM tbl_images i 

Answer 2

Sounds like you need two queries for this: One for counting the votes, and one for counting the comments.

As far as I know, COUNT counts result rows, and joins create result rows to display all allowed permutations of joined tables.

Assuming you have I entries, each with J comments and K votes, you would receive J*K rows for each entry after joins, and COUNTs would both return that J*K instead of the correct amount.

I do not remember if you can do inner queries in MySQL, but that would be the way to go. (See @Kevin Burtons answer)