Re: complicated query (newbie..)

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.

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