asked on
sql server join table to other table depending on column value
i'm using SQL Server 2008.
I have this table called Players which looks like this.
It's a list of players that play in either major league baseball, national football league or national basketball league.
![Players table]()
Notice that it has a column called CoachLeague. That determines what league each player plays in.
Then, if you also notice the column called CoachID, that is the ID of that player's coach.
This is the script to create the Players table:
I also have these 3 tables:
CoachesMLB
![the table called: CoachesMLB]()
This is the script to create the CoachesMLB table:
CoachesNBA
![the table called: CoachesNBA]()
This is the script to create the CoachesNBA table:
CoachesNFL
![the table called: CoachesNFL]()
This is the script to create the CoachesNFL table:
Query 1
So this is the query i have so far:
SELECT [PlayerID]
,[FirstName]
,[LastName]
,[CoachID]
,[CoachLeague]
FROM [TestDatabase].[dbo].[Play
Which returns the players table.
Desired Query Result
I would like to revise Query 1 to add the coache's first name and last name for each player.
So my desired result should look like this. The two columns in blue is what I want to add:
![desired result]()
So I want to look at the value in the CoachLeague column from the Players table and use that value to determine what table to join to and then use the CoachID column to get that players coach.
So for example, in the Players table, the CoachLeague for Bryce Harper is MLB and his CoachID is 15.
So I would get the value for his coach from the CoachesMLB table and get the coach from that table with a CoachID of 15, which would be Dave Martinez.
So for example, in the Players table, the CoachLeague for Lebron James is NBA and his CoachID is 14.
So I would get the value for his coach from the CoachesNBA table and get the coach from that table with a CoachID of 14, which would be Luke Walton.
So for example, in the Players table, the CoachLeague for Derek Carr is NFL and his CoachID is 25.
So I would get the value for his coach from the CoachesNFL table and get the coach from that table with a CoachID of 25 which would be Jon Gruden.
How do I revise my query to get the desired result?
I have this table called Players which looks like this.
It's a list of players that play in either major league baseball, national football league or national basketball league.
Notice that it has a column called CoachLeague. That determines what league each player plays in.
Then, if you also notice the column called CoachID, that is the ID of that player's coach.
This is the script to create the Players table:
USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Players](
[PlayerID] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[CoachID] [int] NULL,
[CoachLeague] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (1, N'Bryce', N'Harper', 15, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (2, N'Madison', N'Bumgarner', 13, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (3, N'Pablo', N'Sandoval', 13, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (4, N'Clayton', N'Kershaw', 6, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (5, N'Lebron', N'James', 14, N'NBA')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (6, N'Draymond', N'Green', 10, N'NBA')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (7, N'Paul', N'George', 21, N'NBA')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (8, N'Klay', N'Thompson', 10, N'NBA')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (9, N'Ezekiel', N'Elliot', 9, N'NFL')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (10, N'Dak', N'Prescott', 9, N'NFL')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (11, N'Derek', N'Carr', 25, N'NFL')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (12, N'Giancarlo', N'Stanton', 25, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (13, N'Aaron', N'Judge', 25, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (14, N'Jose', N'Altuve', 21, N'MLB')
I also have these 3 tables:
CoachesMLB
This is the script to create the CoachesMLB table:
USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CoachesMLB](
[CoachID] [int] NULL,
[Team] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (1, N'Arizona Diamondbacks', N'Torey', N'Lovullo')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (2, N'Atlanta Braves', N'Brian', N'Snitker')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (3, N'Chicago Cubs', N'Joe', N'Maddon')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (4, N'Cincinnati Reds', N'David', N'Bell')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (5, N'Colorado Rockies', N'Bud', N'Black')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (6, N'Los Angeles Dodgers', N'Dave', N'Roberts')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (7, N'Miami Marlins', N'Don', N'Mattingly')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (8, N'Milwaukee Brewers', N'Craig', N'Counsell')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (9, N'New York Mets', N'Mickey', N'Callaway')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (10, N'Philadelphia Phillies', N'Gabe', N'Kapler')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (11, N'Pittsburgh Pirates', N'Clint', N'Hurdle')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (12, N'San Diego Padres', N'Andy', N'Green')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (13, N'San Francisco Giants', N'Bruce', N'Bochy')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (14, N'St. Louis Cardinals', N'Mike', N'Shildt')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (15, N'Washington Nationals', N'Dave', N'Martinez')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (16, N'Baltimore Orioles', NULL, N'Vacant')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (17, N'Boston Red Sox', N'Alex', N'Cora')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (18, N'Chicago White Sox', N'Rick', N'Renteria')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (19, N'Cleveland Indians', N'Terry', N'Francona')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (20, N'Detroit Tigers', N'Ron', N'Gardenhire')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (21, N'Houston Astros', N'A. J.', N'Hinch')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (22, N'Kansas City Royals', N'Ned', N'Yost')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (23, N'Los Angeles Angels', N'Brad', N'Ausmus')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (24, N'Minnesota Twins', N'Rocco', N'Baldelli')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (25, N'New York Yankees', N'Aaron', N'Boone')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (26, N'Oakland Athletics', N'Bob', N'Melvin')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (27, N'Seattle Mariners', N'Scott', N'Servais')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (28, N'Tampa Bay Rays', N'Kevin', N'Cash')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (29, N'Texas Rangers', N'Chris', N'Woodward')
INSERT [dbo].[CoachesMLB] ([CoachID], [Team], [FirstName], [LastName]) VALUES (30, N'Toronto Blue Jays', N'Charlie', N'Montoyo')
CoachesNBA
This is the script to create the CoachesNBA table:
USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CoachesNBA](
[CoachID] [int] NULL,
[Team] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (1, N'Atlanta Hawks', N'Lloyd ', N'Pierce')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (2, N'Boston Celtics', N'Brad', N'Stevens')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (3, N'Brooklyn Nets', N'Kenny', N'Atkinson')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (4, N'Charlotte Hornets', N'James', N'Borrego')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (5, N'Chicago Bulls', N'Fred', N'Hoiberg')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (6, N'Cleveland Cavaliers', N'Larry', N'Drew')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (7, N'Dallas Mavericks', N'Rick', N'Carlisle')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (8, N'Denver Nuggets', N'Michael', N'Malone')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (9, N'Detroit Pistons', N'Dwane', N'Casey')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (10, N'Golden State Warriors', N'Steve', N'Kerr')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (11, N'Houston Rockets', N'Mike', N'D''Antoni')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (12, N'Indiana Pacers', N'Nate', N'McMillan')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (13, N'Los Angeles Clippers', N'Doc', N'Rivers')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (14, N'Los Angeles Lakers', N'Luke', N'Walton')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (15, N'Memphis Grizzlies', N'J. B.', N'Bickerstaff')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (16, N'Miami Heat', N'Erik', N'Spoelstra')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (17, N'Milwaukee Bucks', N'Mike', N'Budenholzer')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (18, N'Minnesota Timberwolves', N'Tom', N'Thibodeau')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (19, N'New Orleans Pelicans', N'Alvin', N'Gentry')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (20, N'New York Knicks', N'David', N'Fizdale')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (21, N'Oklahoma City Thunder', N'Billy', N'Donovan')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (22, N'Orlando Magic', N'Steve', N'Clifford')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (23, N'Philadelphia 76ers', N'Brett', N'Brown')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (24, N'Phoenix Suns', N'Igor', N'Kokoškov')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (25, N'Portland Trail Blazers', N'Terry', N'Stotts')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (26, N'Sacramento Kings', N'Dave', N'Joerger')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (27, N'San Antonio Spurs', N'Gregg', N'Popovich')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (28, N'Toronto Raptors', N'Nick', N'Nurse')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (29, N'Utah Jazz', N'Quin', N'Snyder')
INSERT [dbo].[CoachesNBA] ([CoachID], [Team], [FirstName], [LastName]) VALUES (30, N'Washington Wizards', N'Scott', N'Brooks')
CoachesNFL
This is the script to create the CoachesNFL table:
USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CoachesNFL](
[CoachID] [int] NULL,
[Team] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (1, N'Arizona Cardinals', N'Steve', N'Wilks')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (2, N'Atlanta Falcons', N'Dan', N'Quinn')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (3, N'Baltimore Ravens', N'John', N'Harbaugh')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (4, N'Buffalo Bills', N'Sean', N'McDermott')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (5, N'Carolina Panthers', N'Ron', N'Rivera')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (6, N'Chicago Bears', N'Matt', N'Nagy')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (7, N'Cincinnati Bengals', N'Marvin', N'Lewis')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (8, N'Cleveland Browns', N'Gregg', N'Williams')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (9, N'Dallas Cowboys', N'Jason', N'Garrett')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (10, N'Denver Broncos', N'Vance', N'Joseph')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (11, N'Detroit Lions', N'Matt', N'Patricia')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (12, N'Green Bay Packers', N'Mike', N'McCarthy')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (13, N'Houston Texans', N'Bill', N'O''Brien')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (14, N'Indianapolis Colts', N'Frank', N'Reich')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (15, N'Jacksonville Jaguars', N'Doug', N'Marrone')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (16, N'Kansas City Chiefs', N'Andy', N'Reid')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (17, N'Los Angeles Chargers', N'Anthony', N'Lynn')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (18, N'Los Angeles Rams', N'Sean', N'McVay')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (19, N'Miami Dolphins', N'Adam', N'Gase')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (20, N'Minnesota Vikings', N'Mike', N'Zimmer')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (21, N'New England Patriots', N'Bill', N'Belichick')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (22, N'New Orleans Saints', N'Sean', N'Payton')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (23, N'New York Giants', N'Pat', N'Shurmur')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (24, N'New York Jets', N'Todd', N'Bowles')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (25, N'Oakland Raiders', N'Jon', N'Gruden')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (26, N'Philadelphia Eagles', N'Doug', N'Pederson')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (27, N'Pittsburgh Steelers', N'Mike', N'Tomlin')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (28, N'San Francisco 49ers', N'Kyle', N'Shanahan')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (29, N'Seattle Seahawks', N'Pete', N'Carroll')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (30, N'Tampa Bay Buccaneers', N'Dirk', N'Koetter')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (31, N'Tennessee Titans', N'Mike', N'Vrabel')
INSERT [dbo].[CoachesNFL] ([CoachID], [Team], [FirstName], [LastName]) VALUES (32, N'Washington Redskins', N'Jay', N'Gruden')
Query 1
So this is the query i have so far:
SELECT [PlayerID]
,[FirstName]
,[LastName]
,[CoachID]
,[CoachLeague]
FROM [TestDatabase].[dbo].[Play
Which returns the players table.
Desired Query Result
I would like to revise Query 1 to add the coache's first name and last name for each player.
So my desired result should look like this. The two columns in blue is what I want to add:
So I want to look at the value in the CoachLeague column from the Players table and use that value to determine what table to join to and then use the CoachID column to get that players coach.
So for example, in the Players table, the CoachLeague for Bryce Harper is MLB and his CoachID is 15.
So I would get the value for his coach from the CoachesMLB table and get the coach from that table with a CoachID of 15, which would be Dave Martinez.
So for example, in the Players table, the CoachLeague for Lebron James is NBA and his CoachID is 14.
So I would get the value for his coach from the CoachesNBA table and get the coach from that table with a CoachID of 14, which would be Luke Walton.
So for example, in the Players table, the CoachLeague for Derek Carr is NFL and his CoachID is 25.
So I would get the value for his coach from the CoachesNFL table and get the coach from that table with a CoachID of 25 which would be Jon Gruden.
How do I revise my query to get the desired result?
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
Last Comment
maqskywalker
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks. Good solutions.
SELECT p.[PlayerID]
,p.[FirstName]
,p.[LastName]
,p.[CoachID]
,p.[CoachLeague]
,mlb.[FirstName] AS CoachFirstName
,mlb.[LastName] AS CoachFirstName
,nba.[FirstName] AS CoachFirstName
,nba.[LastName] AS CoachFirstName
,nfl.[FirstName] AS CoachFirstName
,nfl.[LastName] AS CoachFirstName
FROM [TestDatabase].[dbo].[Play
LEFT JOIN [TestDatabase].[dbo].[Coac
ON p.[CoachID] = mlb.[CoachID] AND p.[CoachLeague] = 'MLB'
LEFT JOIN [TestDatabase].[dbo].[Coac
ON p.[CoachID] = nba.[CoachID] AND p.[CoachLeague] = 'NBA'
LEFT JOIN [TestDatabase].[dbo].[Coac
ON p.[CoachID] = nfl.[CoachID] AND p.[CoachLeague] = 'NFL'
But this gives me this;
The columns circled in blue should be combined in only one CoachFirstName column and one CoachLastName column like this: