after 4 days of tries and researchs over the web I can’t make my wishes working.
Any help would be appreciated !
Performed on MYSQL WORKBENCH
I got a DB made of 3 tables :
|id||title||userId (foreign key from user.id)|
|content||postId (foreign key from posts.id)|
|userId (foreign key from user.id)|
I want a query that would take specifics datas from USER and POSTS plus a total count of likes for each post ordered by id of post and a total result of 0 if no likes exist with this id. Here the list of what I want my query to do :
- Select specifics datas in USER and POSTS; ( working )
- COUNT the total of LIKES for each POSTS and give me a total of 0 if any post has no likes;
My Actual Query :
SELECT P.id, P.userId, P.title, P.content, P.date, P.imageUrl, U.username, U.permission, U.image, COUNT(CASE WHEN L.postId = P.id THEN 1 ELSE NULL END) AS totalLikes FROM posts AS P LEFT JOIN user AS U ON U.id = P.userId LEFT JOIN likes AS L ON P.id = L.postId ORDER BY P.id DESC;
This is returning only 1 post with a count of all the likes in database so not what i’m expecting 🙁
Do the aggregation inside
likes only and then join the other tables to the resultset of the aggregation:
SELECT P.id, P.userId, P.title, P.content, P.date, P.imageUrl, U.username, U.permission, U.image, COALECE(L.totalLikes, 0) AS totalLikes FROM posts AS P INNER JOIN user AS U ON U.id = P.userId -- change to LEFT JOIN if the column userId in posts is nullable LEFT JOIN ( SELECT postId, COUNT(*) AS totalLikes FROM likes GROUP BY postId ) AS L ON L.postId = P.id ORDER BY P.id DESC;