Why does this inner join of two tables create duplicate rows

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.

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