Search Postgresql Archives

Re: complicated query (newbie..)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen 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é pour le contenu fourni.






 > 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. <http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009>


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux