Need help with a LEFT OUTER JOIN

[PTW]

Hi Everyone,

I am trying to do a LEFT OUTER JOIN between two tables. Table 1 is an Employee table, and Table 2 has zero or more special custom records for each employee in Table 1. I want to return a row for each Employee in table 1, and also return a value from Table 2 when the idType = 5. If there is no idType = 5 for the employee record, then I still want it to return a row.

Employee Table
EmpNum
1
2
3

Custom Table
EmpNum idType Value
1 1 10
1 5 01/01/99
2 1 16
3 1 12
3 5 05/22/03


The result I want is this:
EmpNum Value
1 01/01/99
2 NULL (or any value)
3 05/22/03

When I try to do a LEFT OUTER JOIN, it does not return my EmpNum = 2 row because there is no corresponding idType = 5 record.


SELECT DISTINCT A.EmpNum, B.Value
from Employee A LEFT OUTER JOIN Custom B
ON A.EmpNum = B.EmpNum and B.idType = 5
order by A.EmpNum

The above query returns a result set that is missing EmpNum = 2
EmpNum Value
1 01/01/99
3 05/22/03

Any suggestions? Thanks!

 

[SQLSister]

Try:

SELECT DISTINCT A.EmpNum, Case when B.Value = 5 then B.Value else Null end As Value from Employee A LEFT OUTER JOIN Custom B
ON A.EmpNum = B.EmpNum 
order by A.EmpNum

Questions about posting. See faq183-874

 

[PTW]

Aha! I had to do a couple of minor changes, but that got me on the right track.

Thank-you--it works perfectly now. Time for me to read up on CASE in a SQL SELECT!

My new code is:

SELECT DISTINCT A.EmpNum,
Case when B.idType = 5 then B.Value
else Null end As Value
from Employee A LEFT OUTER JOIN Custom B
ON A.EmpNum = B.EmpNum and B.idType = 5
order by A.EmpNum