Need to help query with based on condition in SQL Server 2008

Question

I have a question about SQL Server.

Table: Dimemp

          empkey   |  empid | name |loc
            1      |  100   | abc  |Hyd
            2      |  102   | def  |chen
            3      |  -1    | NA   |beng

Table2 : dimdept

        deptkey  |  deptno  | deptname
        1        |  10      |Hr
        2        |  20      |ceo
        3        |  -1      |NA

Transaction table: tranemp

empid    |  deptno   |  projectname |codes
100      |   20      |   test       |100
104      |   10      |   deve       |101
102      |   10      |  test        |107
106      |   40      |   sap        |103
101      |           |  Ca          |100
         |   10      |  manual      |201
100      |   60      |  quality     |100

Here the transedmp table data look into dimensions table if records match exact record then retrieve corresponding keys .if records not match then we need to fill defaule values -1 corresponding key value common columns for traneemp and dimension tables. empid(tranemp)=dimemp(empid) and tranemp(deptno)=dimdept(deptno)

Based on tranemp table I want output like below

    empkey   | deptkey    | projectname   | codes
    1        |  2         |   test        |100
    3        |  1         |   deve        |101
    2        |  1         |   test        |107
    3        |  3         |    sap        |103
    3        |  3         |    Ca         |100
    3        |  1         |   manual      |201
    1        |  3         |  quality      |100

I tried like this:

 select 
     a.empkey, b.deptkey, c.projectname, c.codes 
 from  
     traneemp c 
 inner join
     dimemp  a on a.empid = c.empid 
 inner join  
     dimdept b on b.deptno = c.deptno

I am not able to get expected result. Please tell me how to write query to achieve this task in SQL Server.

Answer 1

You can use left join to get all result even if they don't match and in case they are null get the default value.

select ISNULL(a.empkey, (select empkey from Dimemp where empid = -1)),
       ISNULL(b.deptkey, (select deptkey from dimdept where deptno = -1)), 
       c.projectname, c.codes 
from  traneemp c 
left join dimemp a on a.empid=c.empid
left join dimdept b on b.deptno=c.deptno