I have the following tables:
- Users
- Conversations
- Group_Members
I need to select all the conversations where a user with a specific ID takes part in. Users and Group_Members are in a many-to-many relationship.
Why does the following query create duplicate rows on the last select, as seen in this image?
select * from Conversations select * from Group_Members select Conversations.* from Conversations inner join Group_Members on Group_Members.userid=1054
User.Id
and Conversation.Id
are primary keys.
Sure, select distinct
would work, but I don’t understand why the select above creates duplicates.
Advertisement
Answer
Your join criteria is wrong. It would join them when it sees a Group_members.userId = 1054 regardless what conversations has. You used your “filter criteria” as your “relation criteria”.
Your joining key is in fact ConversationId, and what you used is the filtering.
You should write that as:
select Conversations.* from Conversations inner join Group_Members on Group_Members.ConversationId = Conversations.Id where Group_Members.userid=1054 -- and ConversationId = 4; -- if you would filter for a particular conversation