ChatGPT解决这个技术问题 Extra ChatGPT

MySQL Multiple Left Joins

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.

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.

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

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.


M
Mark Byers

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

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.


DO you have to use the Group By? My rookie double join is not working, and I am trying to find out why.
Thanks - it helped me solve a problem which was hard enough for me to spend 3 hours - solved just in minutes
r
rizon

To display the all details for each news post title ie. "news.id" which is the primary key, you need to use GROUP BY clause for "news.id"

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