Re: complicated query (newbie..)

Martin Gainty wrote:
> could provide greater assistance if you could post the database schema
> you're using

Not sure what you mean by schema (I'm really new to DB world), if you
mean table descriptions from psql, here it is:

reservations=# \d hosts
                                         Table "public.hosts"
            Column           |       Type        |
Modifiers
----------------------------+-------------------+----------------------------------------------------
  id                         | integer           | not null default
nextval('hosts_id_seq'::regclass)
  ip                         | character varying |
  hostname                   | character varying |
  location                   | character varying |
  architecture_id            | integer           |
  os_kind_id                 | integer           |
  os_version_id              | integer           |
  additional_info            | character varying |
  column_12                  | character varying |
  column_13                  | character varying |
  username                   | character varying |
  password                   | character varying |
  alias                      | character varying |
  virtualization_id          | integer           |
  shareable                  | boolean           |
  shareable_between_projects | boolean           |
  notes                      | character varying |
  cpu                        | character varying |
  ram                        | character varying |
  column_24                  | character varying |
  batch                      | character varying |
  asset                      | character varying |
  owner                      | character varying |
  ssh_key_present            | character varying |
  machine_type_model         | character varying |
  mac_address_eth_0          | character varying |
  physical_box               | boolean           |
  up_n_running               | boolean           |
  available                  | boolean           |
  project_id                 | integer           |
Indexes:
     "hosts_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
     "hosts_architecture_id_fkey" FOREIGN KEY (architecture_id)
REFERENCES architecture(id)
     "hosts_os_kind_id_fkey" FOREIGN KEY (os_kind_id) REFERENCES os_kind(id)
     "hosts_os_version_id_fkey" FOREIGN KEY (os_version_id) REFERENCES
os_version(id)
     "hosts_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(id)
     "hosts_virtualization_id_fkey" FOREIGN KEY (virtualization_id)
REFERENCES virtualization(id)

reservations=#
reservations=# \d reservation
                                  Table "public.reservation"
     Column    |       Type        |                        Modifiers
--------------+-------------------+----------------------------------------------------------
  id           | integer           | not null default
nextval('reservation_id_seq'::regclass)
  start_date   | date              |
  end_date     | date              |
  status       | character varying |
  businessneed | character varying |
  notetohwrep  | character varying |
  email_id     | integer           |
  project_id   | integer           |
Indexes:
     "reservation_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
     "reservation_email_id_fkey" FOREIGN KEY (email_id) REFERENCES email(id)
     "reservation_project_id_fkey" FOREIGN KEY (project_id) REFERENCES
project(id)

reservations=#
reservations=# \d reservation_hosts
    Table "public.reservation_hosts"
      Column     |  Type   | Modifiers
----------------+---------+-----------
  reservation_id | integer |
  host_id        | integer |
Foreign-key constraints:
     "reservation_hosts_host_id_fkey" FOREIGN KEY (host_id) REFERENCES
hosts(id)
     "reservation_hosts_reservation_id_fkey" FOREIGN KEY
(reservation_id) REFERENCES reservation(id)




>
> 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
Austauschvon Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mailskoennen wir keine Haftung fuer den Inhalt uebernehmen. 
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec
bontéque pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite.Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant
donnéque les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pourle contenu fourni. 
>
>
>
>
>
>
>  > Date: Thu, 9 Apr 2009 18:08:04 +0200
>  > From: mrkafk@gmail.com
>  > To: sam@samason.me.uk
>  > CC: pgsql-general@postgresql.org
>  > Subject: Re: [GENERAL] 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@postgresql.org)
>  > 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.
> <http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009>