how to inner join three tables and get count

Vote count:

0

I have three tables:

members (id, name, surname, usr_img)

user_uploads (imgID, user_id, filename, description, up_time)

img_likes (likeID, img_id, user_id)

I need to get all from user_uploads, and using the img_id, get uploader info from members and make 2 counts in the img_likes table (check if user_id (from session variable) and img_id exists and get the img total likes).

SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes
FROM user_uploads
INNER JOIN members AS m ON m.id = uu.user_id -- owner info
INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ? -- check if logged in user already liked
INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID -- total likes
GROUP BY img_id, user_id
ORDER BY up_time DESC

I don't need to get nothing from img_id, just count the number of rows. I don't know if an inner join is necessary, maybe to specify the user_id?

and to get the counts:

$usr_liked = row['usr_liked'];
$total_likes = rwo['total_likes'];

Will that work?

Thanks in advance!

asked 2 mins ago

Chazy Chaz

61

how to inner join three tables and get count