Help using left outer joins to include unmatched rows in a query 1

[meumax]

I have two tables, one with employee details including a unique employee "ID". I also have a table where leave details are entered when employees take any type of leave which has a foreign key to the employee ID.

If I do a SELECT statement inner joining the two tables I won't pick up employees who have never taken leave because there will be no related leave entry in the leave table.

A LEFT OUTER JOIN works fine in picking up such employees UNTIL I try to refine the query further by adding a WHERE clause to look only for sick leave entries.

Here's an example of my code:

SELECT
E.Name,
L.LeaveType,
COUNT (E.EmployeeID)
FROM
{oj Employee E LEFT OUTER JOIN LeaveDetails L ON E.EmployeeID = L.fkEmployeeID}
WHERE
L.LeaveType = 'SICK'
GROUP BY
E.Name,
L.LeaveType"
ORDER BY
E.Name ASC

When I add the WHERE L.LeaveType = 'SICK' it causes the query to exclude unmatched rows in the employee table which I don't want. I am trying to report on how many sick leave instances each employee has taken. But I also want to see which employees have not taken any sick leave at all.

 

[tlbroadbent]

Test if L.fkEmployeeID IS NULL. This occurs when no matching record is found in the RIGHT table.

WHERE
L.LeaveType = 'SICK' OR L.fkEmployeeID IS NULL
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jimbopalmer]

Or use a nvl() function to make all nulls into 'SICK'

WHERE
  nvl(L.LeaveType, 'SICK') = 'SICK' I tried to remain child-like, all I acheived was childish.

 

[meumax]

Thanks!

I had the exact same idea EXCEPT that I was testing for NULL on the LeaveType field. Doh!! I should've figured this one out myself.