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.
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*/
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.
SELECT `p`.`id`, `p`.`userId`, IFNULL(`c`.`count`, 0)
FROM `posts` AS `p`
LEFT JOIN (
SELECT `postId`, COUNT(*) AS `count`
GROUP BY `postId`
) AS `c` ON `c`.`postId` = `p`.`id`
rickey_Swift answered on 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.