Unique Unique - 7 months ago 12
SQL Question

How can i count all likes from another tabel?

I already made it to the point that my SQL reutrns all posts from tbl_posts when they have at least one like. BUt now i am wondering how i can get it work so it returns all posts even when they have no likes. The likes are stored in tbl_posts_likes via foreign keys (post_id, user_id as columns in tbl_posts_likes). My SQL looks like this at the moment:

SELECT tbl_posts.*,tbl_users.name,COUNT(tbl_posts_likes.user_id) AS likes
FROM tbl_posts
INNER JOIN tbl_users ON tbl_posts.user_id = tbl_users.id
LEFT JOIN (SELECT * FROM tbl_friends fr WHERE fr.friend_id = '1') AS fr ON tbl_posts.user_id = fr.user_id
RIGHT JOIN tbl_posts_likes ON tbl_posts_likes.post_id = tbl_posts.id
WHERE tbl_posts.user_id = '1' OR tbl_posts.user_id = fr.user_id
ORDER BY tbl_posts.created_at DESC


It would be nice if you can help me out because i am searching since days to get a working SQL set up :/

Greetings from Germany!

Answer

You needed to group by tbl_posts.id to get the results per post. If you dont do this all results are merged into one row.

a fiddle with the results per post, if it has no likes the result is 0.

SELECT tbl_posts.*,tbl_users.name,COUNT(tbl_posts_likes.user_id) AS likes
FROM tbl_posts 
INNER JOIN tbl_users ON tbl_posts.user_id = tbl_users.id 
LEFT JOIN tbl_posts_likes 
ON tbl_posts.id = tbl_posts_likes.post_id
group by tbl_posts.id

A nice way to find solutions for problems like this:

Simplify the problem by first just getting the posts with a count of likes only. later add the other joins but first focus on getting the most basic result.

Comments