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@WeeksTABLE ( WeekIdintIDENTITYPRIMARYKEY ,WeekDateDATETIME )
DECLARE@PlayersTABLE ( PlayerIdintIDENTITYPRIMARYKEY ,PlayerNamevarchar(50) )
DECLARE@ConfirmationsTABLE ( ConfirmationIdintIDENTITYPRIMARYKEY ,PlayerIdint ,WeekIdint )
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@RosterTABLE ( WeekIdint ,WeekDatedatetime ,PlayerIdint ,PlayerNamevarchar(50) ,IsConfirmedvarchar(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 INSERTINTO@Roster ( WeekId ,WeekDate ,PlayerId ,PlayerName ) SELECT w.WeekId ,w.WeekDate ,p.PlayerId ,p.PlayerName FROM @Weeksw ,@Playersp
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=CASEWHENc.PlayerIdISNOTNULLTHEN'Yes'ELSE'No'END FROM @Rosterr [email protected]=c.PlayerIdANDr.WeekId=c.WeekId
And here is your roster! Let’s play some soccer. : )