MySQL 多左连接 [英] MySQL Multiple Left Joins

问题描述

我正在尝试为我正在处理的网站创建一个新闻页面.我决定要使用正确的 MySQL 查询(意思是 COUNT(id) 和 joins 而不是多个查询或 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

另外，为了确定另一件事，我的左加入评论是获取所有帖子的正确方法，无论他们是否有评论，对吗?或者那会是一个正确的加入?哦，最后一件事...如果我将 comments.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屋！