ON condition in LEFT OUTER JOIN doesn't work?!

On Thu, Apr 09, 2009 at 06:34:27PM +0200, Marcin Krol wrote:

SELECT hosts.id, MIN(reservation.start_date)
FROM hosts
LEFT OUTER JOIN reservation_hosts
ON reservation_hosts.host_id = hosts.id
LEFT OUTER JOIN reservation
ON (reservation_hosts.reservation_id = reservation.id AND
reservation.start_date > 2009-04-09)
GROUP BY hosts.id ORDER BY hosts.id

It selects the hosts with reservation.start_date = 2009-04-09 !

You are putting quotes in there, and not comparing to the number 1996
((2009 - 4) - 9), aren't you? Old versions of PG would let this sort of
thing through I think, but newer versions will give an error.

--
Sam http://samason.me.uk/

On 09/04/2009 17:34, Marcin Krol wrote:

reservation.start_date > 2009-04-09)

You need to phrase it like this:

... reservation.start_date > '2009-04-09'::date ...

Try this and see what happens. :-)

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------