SQL query with left join

Question

I need help. I am trying to get the salary and taxable allowances of an employee from the 2 tables, one holds the employee salary and the other his allowance.

The 2 tables share a common column that is the EmpID.

My code only returns a result for the employees that have a taxable allowance.

This is what I have tried:

select  
    Employee.EmpID, amount as Allw, bSalary 
from 
    Employee  
left join
    EmployeeAllowance on (Employee.EmpID = EmployeeAllowance.EmpID)  
where 
    taxStatus = 1 
    and Employee.EmpID = 'PC899'

Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, Jun 16 '16 at 18:06
It's because you are only returning records `WHERE taxStatus=1` — Tab Alleman, Jun 16 '16 at 18:07
The query looks correct, I would only question the WHERE. Try removing one or both of the filters — John Cappelletti, Jun 16 '16 at 18:09
Thanks John. It works but I only want the allowances for the employees with a taxable allowance — Ms_YMG, Jun 16 '16 at 18:14

score 2 · Accepted Answer · answered Jun 16 '16 at 18:08

Without seeing your tables, my guess is that the problem is with your taxStatus = 1 in your WHERE statement. This will drop records where the EmployeeAllowance table doesn't have an entry for the employee. You can change your query to only pull taxStatus=1 records from your EmployeeAllowance table BEFORE left joining it in:

SELECT Employee.EmpID,
    amount AS Allw,
    bSalary
FROM Employee
LEFT JOIN EmployeeAllowance ON 
    Employee.EmpID = EmployeeAllowance.EmpID AND
    employeeAllowance.taxStatus = 1
WHERE Employee.EmpID = 'PC899'

This is equivalent to doing a subquery to restrict that table before joining like:

SELECT Employee.EmpID,
    amount AS Allw,
    bSalary
FROM Employee
LEFT JOIN (SELECT * FROM EmployeeAllowance WHERE taxStatus = 1) as ea ON 
    Employee.EmpID = ea.EmpID 
WHERE Employee.EmpID = 'PC899'

It's just the first one is a little less verbose.

SQL query with left join

1 Answers1