Reputation: 22619
A seemingly simple query that I can't make work...
Two tables room and availability
Room [id, title, beds]
Availability [room_id, date, bed_delta]
A Room has a fixed, maximum number of beds available. Availability describes periods during which Room.beds
is modified (up or down).
I am trying to construct a query that, for a given room and a given start date and duration will sum the beds available.
For example:
If another period of availability is added that overlaps this datetime range and further reduces the beds by 1, the expected result is 0.
It feels like a relatively simple query:
i.e.
SELECT r.beds - coalesce(sum(a.bed_delta), 0) as beds_free
FROM room r
LEFT OUTER JOIN availability a ON (r.id = a.room_id)
WHERE date = '2012-01-01 09:00:00+01:00'
AND r.id = 2
GROUP BY r.id;
This query only returns if there is a matching row in availability
. What I expected was a single row for the room with id == 2.
Upvotes: 3
Views: 4422
Reputation: 51326
The problem is this part of your WHERE
clause:
WHERE date = '2012-01-01 09:00:00+01:00'
An outer join only "allows" rows for which the join condition (here, r.id = a.room_id
) fails; other constraints on the right-hand table that are imposed in the WHERE
clause can still exclude entire rows from the result. Yeah, it's tricky.
Solution: move the date
constraint into the join condition:
LEFT OUTER JOIN availability a ON (r.id = a.room_id AND date = '2012-01-01 09:00:00+01:00')
(Yes, surprisingly enough you can stick arbitrary conditions in there, and as you're now discovering, (for outer joins) this can be necessary!)
Alternatively, you could you use a correlated subquery to find the sum of all the Availability
records that match the Room
of interest:
SELECT r.beds - coalesce((
SELECT sum(a.bed_delta)
FROM availability a
WHERE a.room_id = r.id
AND date = '2012-01-01 09:00:00+01:00'
), 0) as beds_free
FROM room r
WHERE r.id = 2;
I find subqueries to be easier to understand than GROUP
ed outer joins, but YMMV.
Upvotes: 11