Sam Mason wrote:
On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
Sam Mason wrote:
This is a big hint that things are going wrong. You need those quotes
in there, an "integer" is a plain number and not a date.
This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!
Yes, it would do.
Well it does for selecting hosts, but I also want to select the nearest
reservation using r.id like you specified in 'SELECT h.id, r.id,
r.start_date, r.end_date'. I can't do this if r.id is NULL.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date))
The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.
Not backwards, but forward into some reasonable range, like 3 months (I
want the user to see the nearby reservation in future).
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)
these dates are OK.
As a minor point, you shouldn't need to put the "::date" in unless
you're feeling pedantic, PG should figure that out for itself. I put
them in if I'm unsure of what's going on but most of my queries won't
have them in.
Oops! My PG (ver 8.1) does need this ::date suffix!
Regards,
mk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
