StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Don't know why anyone else hasn't said this yet;</p> <p>NORMALISE.</p> <p>You've got a table that models competitions? Competitions are made up of Competitors? You need a distinct list of Competitors in one or more Competitions......</p> <p>You should have the following tables.....</p> <pre><code>CREATE TABLE Competitor ( [CompetitorID] INT IDENTITY(1,1) PRIMARY KEY , [CompetitorName] NVARCHAR(255) ) CREATE TABLE Competition ( [CompetitionID] INT IDENTITY(1,1) PRIMARY KEY , [CompetitionName] NVARCHAR(255) ) CREATE TABLE CompetitionCompetitors ( [CompetitionID] INT , [CompetitorID] INT , [Score] INT , PRIMARY KEY ( [CompetitionID] , [CompetitorID] ) ) </code></pre> <p>With Constraints on CompetitionCompetitors.CompetitionID and CompetitorID pointing at the other tables.</p> <p>With this kind of table structure -- your keys are all simple INTS -- there doesn't seem to be a good NATURAL KEY that would fit the model so I think a SURROGATE KEY is a good fit here.</p> <p>So if you had this then to get the the distinct list of competitors in a particular competition you can issue a query like this:</p> <pre><code>DECLARE @CompetitionName VARCHAR(50) SET @CompetitionName = 'London Marathon' SELECT p.[CompetitorName] AS [CompetitorName] FROM Competitor AS p WHERE EXISTS ( SELECT 1 FROM CompetitionCompetitor AS cc JOIN Competition AS c ON c.[ID] = cc.[CompetitionID] WHERE cc.[CompetitorID] = p.[CompetitorID] AND cc.[CompetitionName] = @CompetitionNAme ) </code></pre> <p>And if you wanted the score for each competition a competitor is in:</p> <pre><code>SELECT p.[CompetitorName] , c.[CompetitionName] , cc.[Score] FROM Competitor AS p JOIN CompetitionCompetitor AS cc ON cc.[CompetitorID] = p.[CompetitorID] JOIN Competition AS c ON c.[ID] = cc.[CompetitionID] </code></pre> <p>And when you have a new competition with new competitors then you simply check which ones already exist in the Competitors table. If they already exist then you don't insert into Competitor for those Competitors and do insert for the new ones.</p> <p>Then you insert the new Competition in Competition and finally you just make all the links in CompetitionCompetitors.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload