how to query multiple junction tables to create 1 record In normalizing data for a Contract bridge tournament, it seems like I am ending up with 13 junction tables. Each tournament will have 2 winners, 2 runner ups, and 4 semi-finalists, and a Tournament director, Assistant Tournament director, and maybe 1-2 Substitutes. For the sake of simplicity, I am working with tables as shown here:



I am trying to create a query which will produce the following result.



Knowing only basic SQL, Querying 1 junction table is not a problem, but 2 or more junction tables has me stumped. Is there a better way to structure my data? I will need to do Insert, Update, and Deletes also. Any help or suggestions will be most welcome.
This answer assumes you can only have one entry per tournament in each of the Winner1Test, Winner2Test, RunnerUp1 and RunnerUp2 tables. You should contrain this with a unique constraint.

SELECT r.TourneyDate,
r.TourneyTime,
r.TournamentID,
w1.Winner1,
w2.Winner2,
r1.RunnerUp1,
r2.RunnerUp2
FROM TournamentResultsTest AS r
LEFT OUTER JOIN Winner1Test AS w1 ON w1.TourneyID = r.TournamentID
LEFT OUTER JOIN Winner2Test AS w2 ON w2.TourneyID = r.TournamentID
LEFT OUTER JOIN RunnerUp1 AS r1 ON r1.TourneyID = r.TournamentID
LEFT OUTER JOIN RunnerUp2 AS r2 ON r2.TourneyID = r.TournamentID


If my assumption is incorrect and each table can have multiple entries per tournament, then you would need to use outer apply (top 1) rather than outer join.