D.0.02 has been allocated for day 1 period 1 so it should count 0. But it has not been allocated for day 1 period 2. Why is it returning as 0?
SELECT COUNT(DISTINCT rm.Id) FROM ts_room rm LEFT JOIN ts_roompref rp ON rp.room_id = rm.id LEFT JOIN ts_request rq ON rq.id = rp.request_id LEFT JOIN ts_allocation a ON a.request_id = rq.id WHERE room_id = "D.0.02" AND (a.status IS NULL OR a.status IN ('Pending', 'Failed', 'Declined')) AND (day_id=1 AND period_id=2)
Here is my fiddle: http://sqlfiddle.com/#!2/5e1de/13
If the status is not Pending, Failed, Declined the room is ignored but it could be free for other period or days but not booked - if it is not booked then it is not allocated - this means that the day_id and period_id will not exist but the room will still be free to book.
It would seem that I would need to retype my SQL query. Any advice on how to?
This post has been edited by jeansymolanza: 19 February 2013 - 04:45 AM