Catching NULL values

[virtual_odin]

I am running this query

SELECT `subordinate`

FROM `people`

LEFT JOIN `hierarchy` ON `people`.`id` = `manager`

LEFT JOIN `absence` ON `person_id` = `subordinate`

WHERE `people`.`id` = 'xxx' AND NOT (NOW() > `start`

AND NOW() < `finish`)

to establish whether the subordinate is in the office today. 

 

Tables are:

• people - person specific data
  
• hierarchy - which has the relationships between manager and subordinate
  
• absence - start and finish dates of absences
  

 

It works fine if there is at least one entry for the subordinate in the absence table, returning the id of only those available.  But it does not return people if they are not in the absence table at all.  The work around is a dummy entry as each user is created, but to me this seems inelegant when it must be possible to modify the query so it also returns values where `subordinate` has no entry at all.

 

Your help would be much appreciated.  I have tried endless permutations of AND NOT OR NULL etc and I just know it is in there somewhere but cannot get my head around it...

[fenway]

Hard to see without table prefixes... but you'll need to add a condition of OR absence.person_id IS NULL".