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:

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?
LVL 1
maqskywalkerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

maqskywalkerAuthor Commented:
i'm kinda close with this query.

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].[Players] p
LEFT JOIN [TestDatabase].[dbo].[CoachesMLB] mlb
    ON p.[CoachID] = mlb.[CoachID] AND p.[CoachLeague] = 'MLB'
LEFT JOIN [TestDatabase].[dbo].[CoachesNBA] nba
    ON p.[CoachID] = nba.[CoachID] AND p.[CoachLeague] = 'NBA'
LEFT JOIN [TestDatabase].[dbo].[CoachesNFL] nfl
    ON p.[CoachID] = nfl.[CoachID] AND p.[CoachLeague] = 'NFL'

But this gives me this;

Q6.PNG
The columns circled in blue should be combined in only one CoachFirstName column and one CoachLastName column like this:

Desired_Result.PNG
SharathData EngineerCommented:
try this.
SELECT p.[PlayerID]
      ,p.[FirstName]
      ,p.[LastName]
      ,p.[CoachID]
      ,p.[CoachLeague]
      ,COALESCE(mlb.[FirstName], nba.[FirstName], nfl.[FirstName])  AS CoachFirstName
      ,COALESCE(mlb.[LastName], nba.[LastName], nfl.[LastName])  AS CoachLasttName
FROM [dbo].[Players] p
LEFT JOIN [dbo].[CoachesMLB] mlb
    ON p.[CoachID] = mlb.[CoachID] AND p.[CoachLeague] = 'MLB'
LEFT JOIN [dbo].[CoachesNBA] nba
    ON p.[CoachID] = nba.[CoachID] AND p.[CoachLeague] = 'NBA'
LEFT JOIN [dbo].[CoachesNFL] nfl
    ON p.[CoachID] = nfl.[CoachID] AND p.[CoachLeague] = 'NFL'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Your joins are good.... although, noticed a lack of PK and/or any indexing....

Might have been worthwhile have all coaches in a single table with their 'league' as an attribute (maybe). Or create a view combining the tables, and expose their league as part of the view, then join once and pick up the name.

However, COALESCE is what you want, It will keep looking for the next "not NULL" column.

Could pick up the wrong league if a name has NULL, wich shouldnt really happen (but has been known to)

The alternative is a CASE statement checking specifically for the league.

SELECT p.[PlayerID]
      ,p.[FirstName]
      ,p.[LastName]
      ,p.[CoachID]
      ,p.[CoachLeague]
      , CASE p.[CoachLeague] when 'MLB' then isnull(mlb.[FirstName],'MLB') + ' ' + isnull(mlb.[LastName],'MLB')     
                             when 'NBA' then  nba.[FirstName] + ' ' + nba.[LastName]
                             when 'NFL' then  nfl.[FirstName] + ' ' + nfl.[LastName]
                             else 'No Idea' end as CoachName  
       -- or repeat the CASE once for firstname, and again for second name if you want two columns.....
       -- and best to use the ISNULL() function to make sure you have some kind of value as above for MLB 

FROM [dbo].[Players] p
LEFT JOIN [dbo].[CoachesMLB] mlb     ON p.[CoachID] = mlb.[CoachID] AND p.[CoachLeague] = 'MLB'
LEFT JOIN [dbo].[CoachesNBA] nba     ON p.[CoachID] = nba.[CoachID] AND p.[CoachLeague] = 'NBA'
LEFT JOIN [dbo].[CoachesNFL] nfl     ON p.[CoachID] = nfl.[CoachID] AND p.[CoachLeague] = 'NFL'

Open in new window


Or, thinking about that VIEW a bit more....
CREATE VIEW VW_Coaches as               
select 'MLB' as LEAGUE ,[CoachID], [Team], [FirstName], [LastName]
FROM [CoachesMLB]
UNION ALL
select 'NBA' as LEAGUE ,[CoachID], [Team], [FirstName], [LastName]
FROM [CoachesNBA]
UNION ALL
select 'NFL' as LEAGUE ,[CoachID], [Team], [FirstName], [LastName]
FROM [CoachesNFL]
GO


SELECT p.[PlayerID]
      ,p.[FirstName]
      ,p.[LastName]
      ,p.[CoachID]
      ,p.[CoachLeague]
      ,c.[FirstName]
	  ,c.[LastName]
FROM [dbo].[Players] p
INNER JOIN VW_Coaches C ON p.[CoachID] = c.[CoachID] AND p.[CoachLeague] = C.LEAGUE

Open in new window

maqskywalkerAuthor Commented:
Thanks. Good solutions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.