Re: complicated query (newbie..)

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