SQL Query using outer join?

Question

I have the following relations:

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

Gordon Linoff · Accepted Answer · 2016-03-03 21:24:09Z

1

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';

answered Mar 3, 2016 at 21:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query using outer join?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related