Skip site navigation (1) Skip section navigation (2)

Re: SQL LEFT JOIN and WHERE

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: Rai Developer <coder(at)montx(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL LEFT JOIN and WHERE
Date: 2008-02-16 21:17:06
Message-ID: 47B752D2.4050604@Sheeky.Biz (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Stephan SzaboDate: 2008-02-17 00:01:21
Subject: Re: SQL LEFT JOIN and WHERE
Previous:From: Rai DeveloperDate: 2008-02-16 20:39:05
Subject: Re: SQL LEFT JOIN and WHERE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group