CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nilesh Gajare
Nilesh Gajare

Reputation: 6398

Inner Join on 3 tables using dynamic columns in SQL

I have three tables

EmployeeConfig contains:

ID  CompanyId  FieldName    Label
---------------------------------------
9   1          DEPARTMENT   DEPARTMENT  
10  1          PTLOCATION   Prof.Tax Location
11  1          DESIGNATION  Designation

Combovalues contains:

ID   CompanyId  FieldName   ComboValue
--------------------------------------------
11         1    DEPARTMENT  Management
12         1    DEPARTMENT  Outsource
13         1    DEPARTMENT  Trims
14         1    DEPARTMENT  Logistics
55         1    DESIGNATION Deputy Executive Director
56         1    DESIGNATION Manager
57         1    DESIGNATION Sr Manager - Trims
58         1    DESIGNATION Manager - Exports
59         1    DESIGNATION Planning Manager

EmployeeDetail contains:

ID   CompanyId  Code    Name Category Department Designation
-------------------------------------------------------------
1          1    AI001   ABC        1    11      55
2          1    S 003   MNO        1    12      56
3          1    S 022   PQR        1    13      57
4          1    S 030   XYZ        1    14      58

EmployeeDetail contains dynamic columns based on EmployeeConfig. I want to replace dynamic columns values with ComboValues in EmployeeDetail table.

I tried this query:

SELECT 
    E.Id, E.NAME,
    C.ComboValue AS Designation,
    Cdep.ComboValue AS department
FROM   
    [Aquara7bc772839].EmployeeDetail AS E
FULL JOIN 
    ComboValues C ON E.Designation = C.Id
FULL JOIN 
    ComboValues Cdep ON E.Department = Cdep.Id
INNER JOIN 
    EmployeeConfig Ex ON E.CompanyId = Ex.CompanyId
WHERE  
    E.CompanyId = 1

But above query is not returning the expected output below:

ID   CompanyId  Code    Name Category   Department      Designation 
-------------------------------------------------------------------------
1          1    AI001   ABC     1       Management      Deputy Executive Director
2          1    S 003   MNO     1       Outsource       Manager
3          1    S 022   PQR     1       Trims           Sr Manager - Trims
4          1    S 030   XYZ     1       Logistics       Manager - Exports

Upvotes: 1

Views: 403

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can do a double join like this:

select e.id,
    e.companyid,
    e.code,
    e.name,
    e.category,
    c1.combovalue department,
    c2.combovalue designation
from EmployeeDetail e
left join Combovalues c1
on e.department = c1.id
and c1.fieldname = 'DEPARTMENT'
left join Combovalues c2
on e.designation = c2.id
and c2.fieldname = 'DESIGNATION';

You can include the further join clauses based on other relationships you have (like company id etc).

Upvotes: 5

Related Questions