MySQL多个左联接 [英] MySQL Multiple Left Joins

问题描述

我正在尝试为我正在工作的网站创建新闻页面.我决定要使用正确的MySQL查询(表示COUNT(id)并加入而不是多个查询或num_rows.)我使用的是PDO包装器，它应该可以正常运行，并且在直接通过MySQL CLI应用程序.

I am trying to create a news page for a website I am working on. I decided that I want to use correct MySQL queries (meaning COUNT(id) and joins instead of more than one query or num_rows.) I'm using a PDO wrapper, that should function fine, and this still fails when run directly through the MySQL CLI application.

基本上，我有3张桌子.一个人持有新闻，一个人持有评论，一个人持有用户.我的目的是创建一个显示所有新闻帖子标题，正文，作者和日期的页面(稍后将进行分页).当我使用第二个查询来获取用户名时，这种方法很好用，但是后来我决定使用JOIN.

Basically, I have 3 tables. One holds the news, one holds the comments and one holds the users. My aim here is to create a page which displays all (will paginate later) the news posts titles, bodies, authors and dates. This worked fine when I used a second query to get the username, but then I decided I'd rather use a JOIN.

那是什么问题?好吧，我需要两个联接.一种是获取作者的用户名，另一种是获取评论数.当我简单地使用作者的用户名时，所有工作都按预期进行.显示新闻表中的所有行(共有2行).但是，当我在评论行中添加第二个LEFT JOIN时，我最终只收到新闻中的一行(请记住，有2条)，而COUNT(comments.id)给我2条(应该显示1条，因为我有每个帖子的评论.)

So what's the problem? Well, I need two joins. One is to get the author's username and the other to get the number of comments. When I simply go for the author's username, all works as expected. All the rows (there are 2) in the news table are displayed. However, when I added this second LEFT JOIN for the comments row, I end up only receiving one row from news (remember, there are 2,) and COUNT(comments.id) gives me 2 (it should display 1, as I have a comment for each post.)

我做错了什么?为什么有两个新闻帖子，每个都有一个评论，为什么它只显示一个新闻帖子并说它有两个评论?

What am I doing wrong? Why is it only displaying one news post, and saying that it has two comments, when there are two news posts, each with one comment?

SELECT news.id, users.username, news.title, news.date, news.body, COUNT(comments.id)
FROM news
LEFT JOIN users
ON news.user_id = users.id
LEFT JOIN comments
ON comments.news_id = news.id

此外，只是要确保有另一件事，我对评论的左加入是获取所有帖子的正确方法，无论它们是否有评论，对吗?还是那是一个正确的加入?哦，最后一件事...如果我将comment.news_id = news.id切换到news.id = comments.news_id，我将得到0个结果.

Also, just to be sure about one other thing, my left join to comments is the correct way to get all posts regardless of whether they have comments or not, correct? Or would that be a right join? Oh, one last thing... if I switch comments.news_id = news.id to news.id = comments.news_id, I get 0 results.

推荐答案

您缺少GROUP BY子句:

You're missing a GROUP BY clause:

SELECT news.id, users.username, news.title, news.date, news.body, COUNT(comments.id)
FROM news
LEFT JOIN users
ON news.user_id = users.id
LEFT JOIN comments
ON comments.news_id = news.id
GROUP BY news.id

左联接是正确的.如果您使用INNER或RIGHT JOIN，那么您将不会获得没有评论的新闻.

The left join is correct. If you used an INNER or RIGHT JOIN then you wouldn't get news items that didn't have comments.

这篇关于MySQL多个左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！