Re: complicated query (newbie..) - Mailing list pgsql-general

Hello Sam,

Thanks a million for reply! I'm so frustrated with this..

Sam Mason wrote:
> On Thu, Apr 09, 2009 at 06:08:04PM +0200, Marcin Krol wrote:
>> What I'm trying to accomplish is producing list of hosts available
>> within a specified timeframe.
>>
>> What I have is a table of hosts, table of reservations (containing id,
>> start_date and end_date) and an association table reservation_hosts.
>>
>> I need a list of hosts, with accompanying reservations fulfilling
>> certain (date-related) conditions.
>>
>> But there are two twists:
>>
>> - if host has reservation(s), but those do not fulfill the date
>> conditions (the host is not available within a specified timeframe), the
>> host obviously should NOT be listed
>>
>> - if host has no reservations at all, it obviously is available, so it
>> should be listed
>
> I think the following should do what you want.
>
>   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 (${window_start},${window_end})
>       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 (${requested_start},${requested_end})
>   ORDER BY h.id, r.start_date)
>
> The formatting is somewhat grim, but I think it should do what you want.

Well it almost works: I see that it selects out the host ids whose date
conditions are not met (while adding those that have no reservations),
but why it produces nothing but NULLs in place of values, even for hosts
who do have reservations but ones ?!

id    id    start_date    end_date
4
    NULL    NULL    NULL
5
    NULL    NULL    NULL
6
    NULL    NULL    NULL
7
    NULL    NULL    NULL
8
    NULL    NULL    NULL
9
    NULL    NULL    NULL
10
    NULL    NULL    NULL
11
    NULL    NULL    NULL
12
    NULL    NULL    NULL
13
    NULL    NULL    NULL




I had to edit it a bit: it seems there was one parentheses missing after
first subquery:

   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  > 2009-04-09)
       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 > 2009-04-09
   ORDER BY h.id, r.start_date)

Two things:

- If I quote date values like '2009-04-09' it doesn't work again! I.e.
result set includes one host id that should have been excluded (bc it
has reservation whose date doesn't match the condition)

- I have replaced OVERLAPS with explicit date condition bc PG complained:

ERROR:  function pg_catalog.overlaps(date, date, integer, integer) does
not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.