1

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:

Tables

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

Dataview

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.

  • 2
    This is not a great design. Instead of all these junction tables, why not just have one table, with a key on TournamentID/MemberID, and what place they came in? – Aaron Bertrand Nov 2 '16 at 1:52
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.