Sql query with joins returns duplicates

Databases
1

I’m trying to get all the comments and messages for a user with this query, but it returns duplicates of the comments. I have two messages in the DB and one comment.

$query = "SELECT *, picture AS avatar FROM users LEFT JOIN comments ON comments.cmt_user = users.id LEFT JOIN messages ON messages.sender = users.id WHERE users.id = ".$_SESSION['id'];
2

there is only one way to avoid dupes in this situation – run two queries, one for comments and one for messages

you can avoid the join in the second query

3

Without more information on database schema or the example results from the query, it’s hard to advise how to solve your problem. Possibly limiting the desired fields in your query, instead of *, could work, but again more information is needed.

4

Two left join creates this because the query find the first rows of the comments table and then foreach row of comment find rows of messages.Use subqueries to fix it.

5

I’m sorry I forgot about this thread. I did however found a different way around it and it seems to work now. Thanks for your replies. Much appreciated.

6

please, do tell us what that solution is

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.