Select columns from a table and count a value from another table

user_19899461 0
Viewed 44

I have the following tables:

table_user

id name
1 Arnold
2 Wesley
3 Abel
4 Harry
5 Cristiano

table_post

fk_user is from user who created the post

id fk_user title description
1 2 Movie I like horror movies
2 4 Music Music is essential to my days
3 4 Soccer The World Cup is coming!

table_post_like

fk_user is from user who liked the post

fk_post fk_user
1 1
1 3
1 4
1 5
2 2
3 3
3 1

I need to select all posts liked by user Arnold (id 1) with all columns from tb_post and number of likes. The result would be something like that:

id fk_user title description likes
1 2 Movie I like horror movies 4
3 4 Soccer The World Cup is coming! 2

I tried this but the number of likes is not correct

SELECT TP.*, COUNT(TPL.fk_post) AS likes
FROM table_post AS TP
RIGHT JOIN  table_post_like AS TPL
ON TP.id = TPL.fk_post
WHERE TPL.fk_user = 1
GROUP BY TP.id
select join postgresql sql
1 Answers

Do this

SELECT table_post.*, t.occurence FROM table_post
JOIN
(SELECT fk_post, COUNT(fk_user) as occurence FROM table_post_like WHERE fk_post IN(SELECT fk_post FROM table_post_like WHERE fk_user = 1)
GROUP BY fk_post) as t ON t.fk_post = table_post.id

Query above returns the value as expected

id fk_user title description likes
1 2 Movie I like horror movies 4
3 4 Soccer The World Cup is coming! 2

Check db fiddle here

user_124588460
Related