CODEWITHSUNDEEP
sqlms-access
SSS
SSS

Reputation: 156

Select From One Table where Does not exist in another

I have One table with People, and I have a second table to record if these people are "Absent" The Absent Table (Absences) has the AbsenceID, PersonID and AbsentDate The People Table has PersonID, FirstName, LastName

I am trying to make an SQL that will Get all the values from the People table, except for those that are Absent that day. So if for example Joe is listed in the Absences table and the AbsentDate is 11/01/2014, I want his name excluded from the query generated. This is what I have so far, but it shows me nothing:

I've tried below, but it returns all the rows:

SELECT * FROM People 
where not exists(Select PersonID 
                 from Absences 
                 where Absences.AbsentDate = 11/01/2014)

I'm sure it's something very simple that I am missing. Any help would be appreciated

Upvotes: 3

Views: 10023

Answers (3)

Jesse C. Slicer
Jesse C. Slicer

Reputation: 20157

Since AbsentDate is a Date/Time field, it is storing both date and time and you are looking for equality on both. So '11/01/2014' is equivalent to searching for '11/01/2014 00:00:00.000', which I can almost bet nothing is stored as. So, the better method is to look for your value between '11/01/2014 00:00:00.0000' and '11/01/2014 23:59:59.999', which is what the BETWEEN operator does.

SELECT *
FROM People
LEFT JOIN Absences ON (People.PersonID = Absences.PersonID) AND (Absences.AbsentDate BETWEEN '11/01/2014' AND '12/01/2014')
WHERE AbsenceID IS NULL

I'm not a fan of NOT IN queries as they can be non-performant in general. But a LEFT JOIN where the joined table's primary key is NULL works quite nicely (assuming appropriate indexing, etc.)

Upvotes: 2

Mujadid
Mujadid

Reputation: 76

Firstly You should mention which database are you using, with current information here is suggested answer You should write query as follow

SELECT * FROM People where PersonID NOT in (Select PersonID 
                   from Absences 
                   where Absences.AbsentDate = sysdate)

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

SELECT * FROM People 
where PersonID NOT IN (Select PersonID 
                       from Absences 
                       where DateValue(Absences.AbsentDate) = '20140111')
                                                                  ^ YYYYMMDD

Upvotes: 7

Related Questions