From: | Marcin Krol <mrkafk(at)gmail(dot)com> |
---|---|
To: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
Cc: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: complicated query (newbie..) |
Date: | 2009-04-09 16:08:04 |
Message-ID: | 49DE1D64.3090200@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | linnewbie | 2009-04-09 16:27:42 | Storing HTML: HTML entities being rendered in that raw form |
Previous Message | Sam Mason | 2009-04-09 15:44:34 | Re: complicated query (newbie..) |