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
DECLARE @Weeks TABLE
(
WeekId int IDENTITY PRIMARY KEY
, WeekDate DATETIME
)
DECLARE @Players TABLE
(
PlayerId int IDENTITY PRIMARY KEY
, PlayerName varchar(50)
)
DECLARE @Confirmations TABLE
(
ConfirmationId int IDENTITY PRIMARY KEY
, PlayerId int
, WeekId int
)
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. : )
