1

I have two tables as below and description is given in below

Employee table

id EmployeeID   departmentid department1   department2 department3 
1    300            100           IT          Finance     
2    400            101           IT          Sales
3    500            102           Technology  Finance 
4    600            101           IT                       HWD

Department table

id  EmployeeID departmentkeys Departmnetvalues
100     300      1                      IT
101     400      1                      IT
102     500      2                      Finance
103     600      1                      IT

In department table, i have departmentkeys column that contains the values 1,1,2,1 and maps to column department1,department2,department3 in the Employee table .

  • 1 means department1
  • 2 means department2
  • 3 means department3

I want to fetch record based on matching records from employee and department table.

select * from Employee e
inner join department d on e.departmentid=d.id
and Case
  When d.departmentkeys=1 then e.department1
  When d.departmentkeys=2 then e.department2 
  When d.departmentkeys=3 then e.department3
End=d.departmentvalues

I do not want to to use CASE statement because its causing a performance issue. Instead of CASE statement i want to use SQL Dynamic query .

closed as off-topic by Philᵀᴹ, Mikael Eriksson, Randi Vertongen, kevinsky, Marco Apr 21 at 18:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, Mikael Eriksson, Randi Vertongen, kevinsky, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

2

As mentioned by Dan Guzman in the comments, you can do this with UNION ALL instead of dynamic SQL:

select * 
from Employee e
    inner join department d 
        on e.departmentid=d.id
where d.departmentkeys=1 AND e.department1 = d.departmentvalues

UNION ALL

select * 
from Employee e
    inner join department d 
        on e.departmentid=d.id
where d.departmentkeys=2 AND e.department2 = d.departmentvalues

UNION ALL

select * 
from Employee e
    inner join department d 
        on e.departmentid=d.id
where d.departmentkeys=3 AND e.department3 = d.departmentvalues;

Akina mentioned another option, which is to use multiple left joins to the same table.

This has the possibility of producing different results if there are any employees that don't have a department, and UNION ALL is probably a better option, performance-wise here. But this is the general approach for the multiple join option:

select * from Employee e
left join department d1 
    on e.departmentid=d1.id and d1.departmentkeys=1 and e.department1=d1.departmentvalues
left join department d2
    on e.departmentid=d2.id and d2.departmentkeys=2 and e.department2=d2.departmentvalues
left join department d3
    on e.departmentid=d3.id and d3.departmentkeys=3 and e.department3=d3.departmentvalues

Not the answer you're looking for? Browse other questions tagged or ask your own question.