Trying to use CTE to replace codes with names-CodePudding

Expecting to get names instead of codes which is highlighted in yellow.

Employee Table

CREATE TABLE [dbo].[_Employees](
    [Name] [nvarchar](50) NULL,
    [Code] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[_Employees] ([Name], [Code]) VALUES (N'A', N'1')
GO
INSERT [dbo].[_Employees] ([Name], [Code]) VALUES (N'B', N'2')
GO
INSERT [dbo].[_Employees] ([Name], [Code]) VALUES (N'C', N'3')
GO

Data Table

CREATE TABLE [dbo].[_Details](
    [Department] [nvarchar](50) NULL,
    [Zone] [nvarchar](50) NULL,
    [Place] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [L1] [nchar](10) NULL,
    [L2] [nchar](10) NULL,
    [L3] [nchar](10) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[_Details] ([Department], [Zone], [Place], [City], [L1], [L2], [L3]) VALUES (N'Department1', N'Zone1', N'Place1', N'City1', N'1         ', N'2         ', N'3         ')
GO
INSERT [dbo].[_Details] ([Department], [Zone], [Place], [City], [L1], [L2], [L3]) VALUES (N'Department2', N'Zone2', N'Place2', N'City2', N'3         ', N'2         ', N'1         ')
GO
INSERT [dbo].[_Details] ([Department], [Zone], [Place], [City], [L1], [L2], [L3]) VALUES (N'Department3', N'Zone3', N'Place3', N'City3', N'2         ', N'3         ', N'1         ')
GO

My Attempt

WITH _Details AS 
( 
    SELECT L1 FROM _Details 
    UNION ALL 
    SELECT e.Name FROM _Employees e INNER JOIN _Details d  ON e.Code = d.L1 
) 
SELECT * FROM _Details

Not sure what I am doing wrong?

CodePudding user response：

Simply JOIN the tables:

SELECT 
   d.[Department], d.[Zone], d.[Place], d.[City], 
   e1.[Name] AS L1, e2.[Name] AS L2, e3.[Name] AS L3
FROM _Details d
LEFT JOIN _Employees e1 ON d.L1 = e1.Code
LEFT JOIN _Employees e2 ON d.L2 = e2.Code
LEFT JOIN _Employees e3 ON d.L3 = e3.Code

CodePudding user response：

You only need joins. Join the employee table thrice, once per employee column. As L1, L2, and L3 are nullable, you need outer joins.

select
  d.[Department], d.[Zone], d.[Place], d.[City],
  e1.[Name] as name1,
  e2.[Name] as name2,
  e3.[Name] as name3
from [dbo].[_Details] d
left join [dbo].[_Employees] e1 on e1.[Code] = d.[L1]
left join [dbo].[_Employees] e2 on e2.[Code] = d.[L3]
left join [dbo].[_Employees] e3 on e3.[Code] = d.[L2];