troubleshooting Question

sql server join table to other table depending on column value

Avatar of maqskywalker
maqskywalker asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
4 Comments2 Solutions133 ViewsLast Modified:
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]

CREATE TABLE [dbo].[Players](
	[PlayerID] [int] NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[CoachID] [int] NULL,
	[CoachLeague] [nvarchar](50) NULL

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:


the table called: CoachesMLB
This is the script to create the CoachesMLB table:

USE [TestDatabase]

CREATE TABLE [dbo].[CoachesMLB](
	[CoachID] [int] NULL,
	[Team] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL

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


the table called: CoachesNBA
This is the script to create the CoachesNBA table:

USE [TestDatabase]

CREATE TABLE [dbo].[CoachesNBA](
	[CoachID] [int] NULL,
	[Team] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL

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


the table called: CoachesNFL
This is the script to create the CoachesNFL table:

USE [TestDatabase]

CREATE TABLE [dbo].[CoachesNFL](
	[CoachID] [int] NULL,
	[Team] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL

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:

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?
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros