Selecting blackout dates through LEFT JOIN and WHERE selector

Question

Hello. Been working on this query for some time and I'm having trouble with a selector. I have a properties table and a dates tables. The dates table contains dates (in a DATE and TIMESTAMP format) that are NOT AVAILABLE for the property. The WHERE clause only selects properties that have entries. This is a problem because it should also select properties that have zero entries, because they are 100% available to rent.

Here is a basic query:

SELECT p.*, p.land_id AS landCode, p.id AS propertyId, d.*, COUNT(d.id) AS land 
FROM `properties` AS p 
LEFT JOIN `dates` AS d ON `p`.`id` = `d`.`land_id` 
WHERE (`d`.`timestamp` BETWEEN '1283317200' AND '1285909199') 
GROUP BY `p`.`id `
ORDER BY `land` ASC

This only returns 1 property (because it has half of September blacked out) and not the other 2 which do not have any dates in September blacked out. If I leave out the WHERE clause, then it will return all properties like I need, but then the date range is not restricted by the user's search.

Answer 1

Try this:

WHERE (d.timestamp IS NULL OR d.timestamp between '1283317200' and '1285909199')

Answer 2

You need to add an "or" expression, directly in the left join. Like this:

SELECT p.*, p.land_id AS landCode, p.id AS propertyId, d.*, COUNT(d.id) AS land  
FROM `properties` AS p  
LEFT JOIN `dates` AS d ON `p`.`id` = `d`.`land_id` and d.timestamp between '1283317200' AND '1285909199'   
GROUP BY `p`.`id ` 
ORDER BY `land` ASC 

Edit: changed the null check so that it is directly (implicitly) in the outer join.

Answer 3

Try putting the criteria within the JOIN

LEFT JOIN `dates` AS d ON `p`.`id` = `d`.`land_id`
    AND (`d`.`timestamp` BETWEEN '1283317200' AND '1285909199')

This should allow all properties to be returned but will only join the ones that are within the date range.

Answer 4

Thanks for you help guys. I decided to add the selector to the actual JOIN instead of the entire query and IT WORKED!

SELECT p.*, p.land_id AS landCode, p.id AS propertyId, d.*, COUNT(d.id) AS land 
FROM `properties` AS p 
LEFT JOIN `dates` AS d ON `p`.`id` = `d`.`land_id` AND `d`.`timestamp` BETWEEN '1283317200' AND '1285909199' 
GROUP BY p.id 
ORDER BY land ASC

This would be the correct query. Thanks again!!