MySQL:简单查询修改

Question

有以下三张表：

1. 帖子
2. posts_replies
3. 点赞

这个查询正在返回我的帖子，他们的回复都很好。

SELECT posts.title, posts.num, posts.status, posts.category, posts.content, posts.member_num, COUNT( posts_replies.blyrb_num ) AS count, COUNT( likes.comment_num ) AS likes_count
FROM posts_replies
INNER JOIN posts ON ( posts_replies.blyrb_num = posts.num )
LEFT JOIN likes ON ( likes.comment_num = posts_replies.num )
WHERE posts.status <>3
AND posts.access = 'Public'
AND (
posts.title LIKE '%dress%'
OR posts.content LIKE '%dress%'
)
GROUP BY posts.num

但问题是，这个查询只返回有一些答复的帖子。我不想要这样的限制，我要所有的帖子，不管他们有没有答复。

我在这里做错了什么有什么想法吗？

谢谢

Answer 1

在“从”和“第一次加入”中交换表，并将第一个“连接”更改为“左联接”，就可以做到这一点：

SELECT posts.title, posts.num, posts.status, posts.category, posts.content, posts.member_num, COUNT( posts_replies.blyrb_num ) AS count, COUNT( likes.comment_num ) AS likes_count
FROM posts
LEFT JOIN posts_replies ON ( posts_replies.blyrb_num = posts.num )
LEFT JOIN likes ON ( likes.comment_num = posts_replies.num )
WHERE posts.status <>3
AND posts.access = 'Public'
AND (
posts.title LIKE '%dress%'
OR posts.content LIKE '%dress%'
)
GROUP BY posts.num

Answer 2

要做的事情：

• 使用LEFT JOIN而不是INNER JOIN
• 交换表：POST和posts_replies

询问，

SELECT  posts.title, 
        posts.num, 
        posts.status, 
        posts.category, 
        posts.content, 
        posts.member_num, 
        COUNT( posts_replies.blyrb_num ) AS count, 
        COUNT( likes.comment_num ) AS likes_count
FROM    posts
        LEFT JOIN posts_replies 
            ON ( posts_replies.blyrb_num = posts.num )
        LEFT JOIN likes 
            ON ( likes.comment_num = posts_replies.num )
WHERE   posts.status <> 3 AND 
        posts.access = 'Public'
        AND 
        (
            posts.title LIKE '%dress%'
            OR posts.content LIKE '%dress%'
        )
GROUP   BY posts.num