|
could provide greater assistance if you could post the database schema you're using cheers (from across the pond) Martin GMT+5(this week) ______________________________________________ Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. > Date: Thu, 9 Apr 2009 18:08:04 +0200 > From: mrkafk@xxxxxxxxx > To: sam@xxxxxxxxxxxxx > CC: pgsql-general@xxxxxxxxxxxxxx > Subject: 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 > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Rediscover Hotmail®: Get e-mail storage that grows with you. Check it out. |
