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

Hello everyone,

I've got this query:

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 !

Regards,
mk

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
------------------------------------------------------------------