Getting data from three tables using join mysql

Question

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

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

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

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)

Have you maintained relation of users table with comments or stories? — Sachin I
– Sachin I, Commented May 13, 2016 at 9:07

Momen Shaker · Accepted Answer · 2017-12-05 09:03:36Z

1

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

answered Dec 5, 2017 at 9:03

Momen Shaker

1391 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

P. Kumar · Accepted Answer · 2016-05-13 09:10:11Z

0

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.

edited May 13, 2016 at 9:10

answered May 13, 2016 at 9:06

P. Kumar

2071 silver badge7 bronze badges

2 Comments

shamon shamsudeen Over a year ago

I will get the error messages unknown column but ccolumn is present\

Strawberry Over a year ago

This will return an indeterminate result.

Collectives™ on Stack Overflow

Getting data from three tables using join mysql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related