CODEWITHSUNDEEP
phpmysql
methuselah
methuselah

Reputation: 13206

SQL count value returning 0 even though room is not allocated

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?

Upvotes: 0

Views: 235

Answers (3)

Meherzad
Meherzad

Reputation: 8553

The problem is that your dayId and period_id are varchar and you are comparing it as numeric

Update the last line of your query and also add records for that day_id and period_id so that you can get result.

AND (day_id like "1" AND period_id like "2")

Updated

 select 1- count(distinct rp.Id) from 
ts_roompref rp, ts_request rq, ts_allocation ta
where rp.room_id= "D.0.02" AND rq.id = rp.request_id and
ta.request_id = rq.id AND ta.status not in ("Pending", "Declined", "Failed")
AND day_id like "1" AND period_id like "5";

Check http://sqlfiddle.com/#!2/5e1de/187

Hope this helps

Upvotes: 0

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Count() always returns a number even when no rows match. In your case, no rows match, so count() returns 0.

Edit:

Your select counts the number of bookings. 0 means that it is unbooked.

If you want to return 1 when it is unbooked you need to reverse it: 

SELECT 1 - 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 = 'Allocated'
       AND day_id=1 AND period_id=2

Note that if you make a wider search, for instance all periods in a day, you need to change the 1 to the number of periods (9) to return the number of free slots.

Upvotes: 2

sybear
sybear

Reputation: 7784

The status of the room D.0.02 is "Allocated".

a.status IN ('Pending', 'Failed', 'Declined') = false

a.status IS NULL = false

The result of statement WHERE becomes false.

No rooms are found.

Upvotes: 0

Related Questions