Sql query for fetching the post and number of likes?

Need one query to get data from posts table and post_likes. While the data in post_likes may not be. Without using the JOIN shows only those records where there is post_likes, using says JOIN not unique table/alias.

Structure post_likes:

CREATE TABLE post_likes (
postId INT UNSIGNED NOT NULL, /*postId - id of the post*/
userId INT UNSIGNED NOT NULL, /*userId - user id*/
UNIQUE KEY(postId, userId) /*a combination of the postId, userId must be unique*/
);

Query:

SELECT posts.id posts.userId, COUNT(post_likes.postId)
FROM posts, post_likes
LEFT JOIN posts ON posts.id = post_likes.postId
WHERE posts.id = post_likes.postId
GROUP BY posts.id;

Help to modify the query so that it correctly worked.
July 9th 19 at 13:42
2 answers
July 9th 19 at 13:44
Solution
SELECT `p`.`id`, `p`.`userId`, IFNULL(`c`.`count`, 0)
 FROM `posts` AS `p`
 LEFT JOIN (
 SELECT `postId`, COUNT(*) AS `count`
 FROM `post_likes`
 GROUP BY `postId`
 ) AS `c` ON `c`.`postId` = `p`.`id`
So, here is an idea that was one request - Logan93 commented on July 9th 19 at 13:47
: This is one request. In that form, but not the fact that will be faster.
SELECT `p`.`id`, `p`.`userId`, COUNT(`pl`.`postId`)
 FROM `posts` AS `p`
 LEFT JOIN `post_likes` AS `pl` ON `pl`.`postId` = `p`.`id`
 GROUP BY `p`.`id`
- Kenyatta commented on July 9th 19 at 13:50
Thank you! - Logan93 commented on July 9th 19 at 13:53
July 9th 19 at 13:46
You likes to do LEFT JOIN posts?) According to the logic of the post may not be likes, so you need to posts to do a LEFT JOIN likes. And why WHERE you duplicate the condition on which the JOIN is already done?
You, young man, to spend 15 minutes reading at least one article about JOIN in the RBD, it would be much more useful than the toaster questions to write.
I actually did no WHERE, just got confused, because I some mistake - Logan93 commented on July 9th 19 at 13:49

Find more questions by tags DatabasesSQLMySQL