Re: complicated query (newbie..)

Sam Mason wrote:
> On Thu, Apr 09, 2009 at 04:47:32PM +0200, Marcin Krol wrote:
>> I've got 3 tables: hosts (with host.id column) and reservation (with
>> reservation.id column) in many-to-many relation, and reservation_hosts
>> which is an association table (with reservation_id and host_id columns).
>>
>> So I've got this query which selects hosts and reservations under
>> certain conditions:
>
> If you could describe what you want in words it would help more.  I
> think you want something like "I was a list of all hosts and their first
> reservation that doesn't cover some specific date".

It's somewhat complicated:

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


> If that's correct; you've got a couple of choices, either turn the inner
> join into an outer join and move it up to join onto the hosts, or get
> rid of it completely and use the DISTINCT ON clause.

I'll try doing smth with it..

Regards,
mk