CODEWITHSUNDEEP
sql
Adil Sari
Adil Sari

Reputation: 175

SQL Query: Customer Table Contains Different type of EmpIDs and i want to get their name in a single row

I got a customer table and each customer has 4 different employeeID (tranieeID,RepresenterID,CoridatorID and ManagerID) I want to get all these employees name rather then their ID in single row.

CustomerTable
|CustomerID|CustomerName|tranieeID|RepresenterID |CoridatorID |ManagerID
------------------------------------------------------------------------
 01          Mr T          100         101           102         103



EmployeeTable
EmpID | EmpName
---------------
100     Mr A
101     Mr B
102     Mr C
103     Mr D

What I need

CustomerID | CustomerName | tranieeName | RepresenterName | CoridatorName | ManagerName
----------------------------------------------------------------------------------------
01            Mr T              Mr A           Mr B              Mr C            Mr D

I did inner join but I got 4 Rows, is there any way to get all these with a single row? Thank you for your help!

Upvotes: 1

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

JOIN should work. I would recommend LEFT JOIN in case any of the values are not filled in:

select c.*,
       et.empname as traineeName,
       er.empname as RepresenterName,
       ec.empname as CoridatorIDName,
       em.empname as ManagerName
from customertable c left join
     employeetable et
     on c.traineeID = et.empid left join
     employeetable er
     on c.RepresenterID = et.empid left join
     employeetable ec
     on c.CoridatorID = ec.empid left join
     employeetable em
     on c.ManagerID = em.empid

Upvotes: 1

Stu
Stu

Reputation: 32619

You could use a correlated subquery in each instance, such as

select c.CustomerId, CustomerName,
  (select Empname from EmployeeTable e where e.EmpID=c.TranieeId) TraineeName,
  (select Empname from EmployeeTable e where e.EmpID=c.RepresenterId) RepresenterName,
  ... etc
from CustomerTable c

Upvotes: 0

Related Questions