CODEWITHSUNDEEP
phpmysql
geeksalah
geeksalah

Reputation: 51

Mysql left join return duplicate rows

Sorry if my question is a bit stupid one. but I am new to mysql database. I have two tables eg Post and comment. I am trying to get a post from table Post and all its comments from table Comment . what I have tried so far is."SELECT post.*,comment.username, comment.comment FROM post LEFT JOIN comment ON post.id = comment.post_id ORDER BY post.id DESC LIMIT 10";.

Now the problem is : eg. If there are 5 comments on post 1. it will result in something like.

post 1 -> comment 1
post 1 -> comment 2
post 1->comment 3 and so on.
While i am trying to get something like

post1 -> comment 1, comment 2, comment 3..

I don't want to do something for this problem in the application layer. Thanks

Upvotes: 1

Views: 727

Answers (1)

user4018366
user4018366

Reputation:

Your result is correct because the JOIN statement merge two tables in one recordset (because you have many records into comment table related to only one record in post table).

If you want to get al comments for single post you need execute a dedicate query to get all comments for related topic, for example:

SELECT * FROM comment WHERE post_id = YOUR_POST_ID LIMIT 10

Cheers

Upvotes: 1

Related Questions