Need SQL statement to return records that return Null

Question

Here's the three tables I'm working with:

This is the SQL statement I'm working with:

SELECT DISTINCT Employees.Corps_ID, Employees.Last_Name, Employees.First_Name, Employees.Home_Phone, Employees.Mobile_Phone
FROM Shifts INNER JOIN (Employees INNER JOIN Employees_Shifts ON Employees.Employee_ID = Employees_Shifts.Employee_ID) ON Shifts.Shift_ID = Employees_Shifts.Shift_ID
WHERE Shifts.Start_Date_Time Between #11/1/2015# And #12/1/2015# And  Employees_Shifts.Employee_ID = NULL ;

What I really need this query to do is examine all the Shifts over the date range indicated and return only those Employee records that don't match up.

I've tried changing the INNER operator to LEFT in a couple places but Access just keeps complaining and won't run the query. I definitely need some help with this one. Please advise.

Antonín Lejsek · Accepted Answer · 2015-11-26 16:57:45Z

1

SELECT Employees.Corps_ID, Employees.Last_Name, Employees.First_Name, Employees.Home_Phone, Employees.Mobile_Phone
FROM Employees 
WHERE NOT EXISTS(
    SELECT * from Employees_Shifts 
    JOIN Shifts ON Shifts.Shift_ID = Employees_Shifts.Shift_ID
    WHERE Shifts.Start_Date_Time Between #11/1/2015# And #12/1/2015# 
    AND Employees_Shifts.Employee_ID = Employees.Employee_ID
    )

Btw DISTINCT often indicate some problem with schema or query, always ask why You need it.

answered Nov 26, 2015 at 16:57

Antonín Lejsek

6,1132 gold badges18 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Need SQL statement to return records that return Null

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