We help IT Professionals succeed at work.
Get Started

sql server join table to other table depending on column value

132 Views
Last Modified: 2021-04-21
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:

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')

Open in new window



I also have these 3 tables:

CoachesMLB

the table called: 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')

Open in new window



CoachesNBA

the table called: 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')

Open in new window



CoachesNFL

the table called: 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')

Open in new window



Query 1

So this is the query i have so far:

SELECT [PlayerID]
      ,[FirstName]
      ,[LastName]
      ,[CoachID]
      ,[CoachLeague]
FROM [TestDatabase].[dbo].[Players]

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?
Comment
Watch Question
Data Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 4 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE