CODEWITHSUNDEEP
sqlsql-serverjoinleft-join
Goolsy
Goolsy

Reputation: 237

Left Excluding join not working

I have a table that tracks when a worker clocks in and out of a factory. There is also a corresponding table that tracks a worker's roster. I need to know when a worker has clocked in without a corresponding roster. i.e. they worked when they were not rostered.

Example Table data:

Clocks:

Name     Date              StartTime       EndTime
Smith    2013/06/25        09:00           17:00
Smith    2013/06/26        09:03           17:01
Smith    2013/06/27        08:58           16:57
Brown    2013/06/25        09:00           17:00

Roster:

Name     Date              StartTime       EndTime
Smith    2013/06/25        09:00           17:00
Smith    2013/06/27        09:00           17:00

Therefore I need to see Smith for 2013/06/26 and Brown 2013/06/25 as there was no roster for either worker on a day that they worked.

My script below is not working - I just get a blank return. Any ideas why?

SELECT * FROM clocks c
LEFT JOIN roster r1
ON clocks.date = roster.date
Left join roster r2
on c.name = r2.name
WHERE r1.date IS NULL

Upvotes: 1

Views: 686

Answers (1)

Ben Reich
Ben Reich

Reputation: 16324

You just want to join to the rosters table once:

SELECT c.name, c.date
FROM clocks c
LEFT JOIN roster r
    ON c.date = r.date
    AND c.name = r.name
WHERE r.date IS NULL

This query return all name/dates from the clocks table where there is no corresponding row in the rosters table. Only 1 join (with 2 parts to the join predicate) is necessary.

Upvotes: 1

Related Questions