CODEWITHSUNDEEP
postgresqlouter-join
Rob Cowie
Rob Cowie

Reputation: 22619

Postsgresql Outer Join not working as intended

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

Answers (1)

j_random_hacker
j_random_hacker

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 GROUPed outer joins, but YMMV.

Upvotes: 11

Related Questions