Re: complicated query (newbie..)

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.