SQL – Combining Multiple Tables to Create a Roster

In this example, I am combining the data from three tables.  Two of the tables have different columns and no relation or join. 

I am managing a soccer team.  I have a list of weeks in the soccer season.  And I also have a list of players.  In addition, I also have a list of weeks in which each player have confirmed. 

DECLARE
@Weeks TABLE
(
       WeekId int IDENTITY PRIMARY KEY
       , WeekDate DATETIME
)

Weeks.PNG

DECLARE @Players TABLE
(
       PlayerId int IDENTITY PRIMARY KEY
       , PlayerName varchar(50)
)

Players.PNG
DECLARE @Confirmations TABLE
(
       ConfirmationId int IDENTITY PRIMARY KEY
       , PlayerId int
       , WeekId int
)
Confirmations.PNG
Using Transact SQL, I want to create a roster that combines all of the players and all of the weeks and specifies whether a player has confirmed participation for that week.
— declare result table
DECLARE @Roster TABLE
(
       WeekId int
       , WeekDate datetime
       , PlayerId int
       , PlayerName varchar(50)
       , IsConfirmed varchar(3)
)
To combine all Players and all Weeks, you simply perform a select without a join.
— combine all weeks and all players to create roster
INSERT INTO @Roster
(
       WeekId
       , WeekDate
       , PlayerId
       , PlayerName
)
SELECT
       w.WeekId
       , w.WeekDate
       , p.PlayerId
       , p.PlayerName
FROM
       @Weeks w
       , @Players p
Once you have your roster, you can perform a quick update to add whether the player has confirmed for each of the weeks.
— perform multiple update for confirmations’
UPDATE
    @Roster
SET
    IsConfirmed = CASE WHEN c.PlayerId IS NOT NULL THEN ‘Yes’ ELSE ‘No’ END
FROM
    @Roster r
    LEFT OUTER JOIN @Confirmations c ON r.PlayerId = c.PlayerId AND r.WeekId = c.WeekId
And here is your roster!  Let’s play some soccer.  : )
Roster.PNG

 
 
 

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.