Re: SQL LEFT JOIN and WHERE

Rai Developer wrote:

> maybe this also works, and I use only two tables:
>
> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND c.id > 0) AND
> (r.date_in <= '2008-02-15') AND (r.date_out >= '2008-02-15') ORDER
> BY order_position
>
> but again, I think the WHERE clausule is affecting the LEFT JOIN ....
> and I'm only getting the cages with some reservation ...
>
> any idea on how to change it for showing all the cages, no matter if
> they have reservation or not ?

So I take it that date_out will be the date it goes to the customer and
date_in is when it returns to you.

I had a bit more of a look this time and tested this one -

SELECT c.*,r.*

FROM cages c
LEFT JOIN reserved_cages r ON (c.id=r.cage_id)
AND ('2008-02-15' BETWEEN r.date_out AND r.date_in)

WHERE ( c.cages_type_id=1 AND c.id > 0)

ORDER BY order_position

Two things I can think of this way - is if the cage is advanced reserved
for 208-02-17 - 2008-02-19 and you search for 2008-02-15 and they want
it from 2008-02-15 for four days you won't see the advanced booking
which will clash.
Also if the date_out is set but not the date_in you won't see that it is
out indefinitely.

So you may want to use sub-selects (I merged the reservation detail into
one column for simplicity) -

SELECT c.*,
(SELECT r.date_out||' '||r.customer
FROM reserved_cages r
WHERE c.id=r.cage_id
AND ( ('2008-02-15' BETWEEN r.date_out AND r.date_in)
OR (r.date_out<='2008-02-15' AND r.date_in IS NULL)
OR (r.date_out>='2008-02-15')
) ORDER BY r.date_out LIMIT 1 ) as reserved

FROM cages c

WHERE ( c.cages_type_id=1 AND c.id > 0)

ORDER BY order_position

(I'm sure some of the pro's can come up with something better though)

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz