how to query multiple junction tables to create 1 record

Question

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.

Answer 1

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.