I have the following Tables:
我有以下表格:
posts
post_id | user_id | text
1 | 12 | blabla
2 | 64 | sususus
and
comments
comment_id | post_id | user_id | text
1 | 1 | 55 | I like this...
2 | 2 | 66 | Yeah, me also!
...
Now posts and comments are connected by the post_id id. How can I get all posts that have less than 18 comments?
现在帖子和评论通过post_id id连接。如何获得少于18条评论的所有帖子?
1 个解决方案
#1
0
SELECT p.*
FROM posts p
LEFT JOIN (
SELECT post_id, COUNT(*) AS total
FROM comments
GROUP BY post_id
) p2
ON p.post_id = p2.post_id
WHERE p2.total < 18 OR p2.total IS NULL
Here you're using a subquery to get all the post_ids and the number of comments for each post. You then connect this to the posts table using LEFT JOIN so your results include posts which have no comments.
在这里,您使用子查询来获取每个帖子的所有post_ids和注释数量。然后使用LEFT JOIN将其连接到posts表,以便您的结果包含没有注释的帖子。
#1
0
SELECT p.*
FROM posts p
LEFT JOIN (
SELECT post_id, COUNT(*) AS total
FROM comments
GROUP BY post_id
) p2
ON p.post_id = p2.post_id
WHERE p2.total < 18 OR p2.total IS NULL
Here you're using a subquery to get all the post_ids and the number of comments for each post. You then connect this to the posts table using LEFT JOIN so your results include posts which have no comments.
在这里,您使用子查询来获取每个帖子的所有post_ids和注释数量。然后使用LEFT JOIN将其连接到posts表,以便您的结果包含没有注释的帖子。