CODEWITHSUNDEEP
sqlpostgresqljoinleft-joinouter-join
Arthur L
Arthur L

Reputation: 3

SQL Query using outer join?

I have the following relations:

enter image description here

and I want to to list all employees that joined the company before June of 2014 but did not receive a commission in June 2014 using some kind of outer join. I came up with this query but it is not working. Could someone tell me how to query this?

SELECT DISTINCT Employee.EmpId, Employee.EmpName
FROM Employee
LEFT OUTER JOIN Commission
ON Employee.EmpId = Commission.EmpId
WHERE Employee.JoinDate BETWEEN Employee.JoinDate AND '2014-06-31'
GROUP BY Employee.EmpId, Employee.EmpName
HAVING COUNT(Commission.Commdate BETWEEN '2014-06-01' AND '2014-06-31') = 0
ORDER BY Employee.EmpId

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The LEFT JOIN is a good idea. But, you want the commission dates in the ON clause. Then find the employees that do not match. So, here is a version of the query (cleaned up to use table aliases):

SELECT e.EmpId, e.EmpName
FROM Employee e LEFT OUTER JOIN
     Commission c
     ON e.EmpId = c.EmpId AND
        c.Commdate BETWEEN '2014-06-01' AND '2014-06-31'
WHERE e.JoinDate < '2014-06-01' AND c.EmpID IS NULL
GROUP BY e.EmpId, e.EmpName
ORDER BY e.EmpId;

Perhaps a more natural way to write the query, though, is:

SELECT e.*
FROM employee e
WHERE NOT EXISTS (SELECT 1
                  FROM Commission c
                  WHERE e.EmpId = c.EmpId AND
                        c.Commdate BETWEEN '2014-06-01' AND '2014-06-31'
                 ) AND
       e.JoinDate < '2014-06-01';

Upvotes: 1

Related Questions