I have a select statement that requires me to join multiple tables (4 tables).

My tables are the following:

1. Teams

2. Team_User

3. Tournament_User

4. Tournaments

I need to get the teams from a certain tournament. My logic is at it follows:

In Tournament_User table i can find the users that are in a tournament. In Team_User i can find the users that are in a team.

To get the teams from a certain tournament I tried the following query:

SELECT t.id FROM Teams t
JOIN Team_User tu on tu.team_id = t.id
JOIN Tournament_User tru on tru.user_id = tu.user_id
JOIN Tournaments tr on tr.id = tru.tournament_id
WHERE tr.id = "tournamentId";

It gets me the correct teams, but it duplicates them.

I also added DISTINCT which it gets me the correct teams and without duplicating them, but I wonder if I can retrieve the records as expected using only joins and without DISTINCT.

Also, my records can't contain duplicates and there are no duplicates, I somehow managed to bring them duplicated based on my query.

CodePudding user response：

I presume there is a Users table in your schema. There is a many-to-many relation between Teams and Users as well as a many-to-many relation between Users and Tournaments. That means each tournament will be related to many users, which in turn means that even if all users are from the same team, your query result will have each team as many times as there are users from it in the given tournament. The nature of the relations between these tables necessitates that you use DISTINCT.