Re: complicated query (newbie..)

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.

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

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

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