Employee -
Employee (Number) - (10 distinct records)
Department (Char)
Manager -
Employee Number (Char) - (3 records matching with TableA)
Employee Name(Char)
Manager (char)
Requirement:- Select the list of all employee numbers,Employee Name. For normal employees, employee name can be blanks while for managers select the correct name from Manager table.
I wrote the following query to achieve the same using a Left Outer Join.
Select A.EmpNo,ifnull(B.EmpName,' ') from Employee A Left Outer Join
Manager B On A.EmpNo = B.EmpNo Where B.IsManager = 'Y'
This query should ideally return 10 records since a Left Outer Join is done. But here
the query would return only 8 records i.e. it would function like an Inner Join.
Why:- Here the where condition is applied on the Joined table. Since for the mis- matching rows the value is null, the where condition would fail hence that row would be omitted. Here is an alternate solution
Select A.EmpNo,ifnull(B.EmpName,' ') from Employee A Left Outer Join
(Select * From Manager Where IsManager = 'Y') B On A.EmpNo = B.EmpNo This would work as there is no where condition applied on the null columns.
Thanks Lynne Roll and all other System i Network users for this piece of learning.
Click here to go to the System i Network thread. ; Forum Post
