[jeansymolanza]

I'm having problems with my SQL query again...

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

[modi123_1]

Something that annoy me.
ts_room.. id.. why are you using varchars? Ugh.. Numbers! Numbers! They are substantially faster on the joins (in large groups), and can be forced to not repeat.

This whole 'period' thing where you divide up the day.. how many periods are there in a day, and why are you not subtracting that from the count per each room from that day?

Also your ts_request - if this is the table to show your room is reserved why doesn't it include the date the room is supposed to be reserved as well as the room?

Your whole structure makes no sense, and, honestly, you may be better off scrapping it and reworking it.