使用join mysql从三个表中获取数据

I have three tables in my database

我的数据库中有三个表

1.Users (id,username)

2.Stories (id,user_id,content) here user_id is forigen key refers Users table id

2.Stories(id,user_id,content)这里user_id是forigen key是指users表id

3.Comments (id,user_id,story_id,comment) here user_id is forigen key refers Users table id and story_id is forigen key refers Stories table id

3.评论(id,user_id,story_id,comment)这里user_id是forigen key是指users表id和story_id是forigen key是指Stories表id

I need to get list of stories from Stories table with total number of comments on that post and username of the storie author

我需要从Stories表中获取故事列表,其中包含该帖子的评论总数和storie作者的用户名

Here is my query for that

这是我的查询

SELECT stories.id,stories.content,COUNT(stories.id) as totalcomment
FROM stories
 JOIN comments
ON stories.id=comments.story_id GROUP BY stories.id

I will get the total comment of the each post ,but can't fetch the username of the storie author (ie username from Users table)

我将获得每篇文章的总评论,但无法获取storie作者的用户名(即来自Users表的用户名)

2 个解决方案

#1

Try this:

SELECT stories.id,stories.content,COUNT(stories.id) as totalcomment, Users.username
FROM stories, Users, comments
WHERE stories.id=comments.story_id
AND stories.user_id = Users.id
GROUP BY stories.id

Or with join:

或者加入:

SELECT stories.id,stories.content,COUNT(stories.id) as totalcomment, Users.username
FROM stories
JOIN comments ON stories.id=comments.story_id
JOIN users ON stories.user_id = Users.id
GROUP BY stories.id

Hope this works.

希望这有效。

#2

SELECT s.id,s.content,COUNT(c.story_id) as totalcomment, u.username
FROM stories s LEFT JOIN comments c ON (s.id=c.story_id)
LEFT JOIN users u ON (s.user_id = u.id)
GROUP BY s.id

#1