-1

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

My tables are the following:

  1. Teams

enter image description here

  1. Team_User

enter image description here

  1. Tournament_User

enter image description here

  1. Tournaments

enter image description here

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.

poPaTheGuru
  • 1,009
  • 1
  • 13
  • 35
  • Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) – philipxy Mar 30 '22 at 08:41
  • This is a faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research. Please in code questions give a [mre]. [ask] [Help] Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Mar 30 '22 at 08:43

1 Answers1

3

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.

András Hummer
  • 960
  • 1
  • 17
  • 35