attachments/rhutton/lists/photos/weeks.png

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