Thread: complicated query (newbie..)
Hello everyone, 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: SELECT * FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id INNER JOIN (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date <= 2009-04-10 AND r.start_date < 2009-04-09) OR (r.start_date = 2009-04-09 AND r.end_date <= 2009-04-10) OR r.start_date > 2009-04-09 )) GROUP BY rh.host_id) AS min_date(host_id, start_date) ON hosts.id = min_date.host_id AND reservation.start_date = min_date.start_date ORDER BY hosts.id, reservation.start_date Great. But I need to add to this table *hosts which have no reservations at all* as well. If I change INNER JOIN to LEFT OUTER JOIN, it adds the hosts I need, but it also lists every reservation, not just those from the subquery. I tried using another LEFT OUTER JOIN with additional query (which is some arbitrary host selection that will do for the moment) but it doesn't work: SELECT hosts.id AS hosts_id, hosts.ip AS hosts_ip, hosts.hostname AS hosts_hostname, hosts.location AS hosts_location, hosts.architecture_id AS hosts_architecture_id, hosts.os_kind_id AS hosts_os_kind_id, hosts.os_version_id AS hosts_os_version_id, hosts.additional_info AS hosts_additional_info, hosts.column_12 AS hosts_column_12, hosts.column_13 AS hosts_column_13, hosts.username AS hosts_username, hosts.password AS hosts_password, hosts.alias AS hosts_alias, hosts.virtualization_id AS hosts_virtualization_id, hosts.shareable AS hosts_shareable, hosts.shareable_between_projects AS hosts_shareable_between_projects, hosts.notes AS hosts_notes, hosts.cpu AS hosts_cpu, hosts.ram AS hosts_ram, hosts.column_24 AS hosts_column_24, hosts.batch AS hosts_batch, hosts.asset AS hosts_asset, hosts.owner AS hosts_owner, hosts.ssh_key_present AS hosts_ssh_key_present, hosts.machine_type_model AS hosts_machine_type_model, hosts.mac_address_eth_0 AS hosts_mac_address_eth_0, hosts.physical_box AS hosts_physical_box, hosts.up_n_running AS hosts_up_n_running, hosts.available AS hosts_available, hosts.project_id AS hosts_project_id, reservation.id AS reservation_id, reservation.start_date AS reservation_start_date, reservation.end_date AS reservation_end_date, reservation.status AS reservation_status, reservation.businessneed AS reservation_businessneed, reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id AS reservation_email_id, reservation.project_id AS reservation_project_id FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON hosts.id = reservation_hosts_1.host_id LEFT OUTER JOIN reservation ON reservation.id = reservation_hosts_1.reservation_id LEFT OUTER JOIN (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date <= 2009-04-10 AND r.start_date < 2009-04-09) OR (r.start_date = 2009-04-09 AND r.end_date <= 2009-04-10) OR r.start_date > 2009-04-09 )) GROUP BY rh.host_id) AS min_date(host_id, start_date) ON hosts.id = min_date.host_id AND reservation.start_date = min_date.start_date LEFT OUTER JOIN (SELECT hosts.id FROM hosts WHERE hosts.id IN (10, 11, 12)) AS nullresv(host_id) ON hosts.id = nullresv.host_id ORDER BY hosts.id, reservation.start_date Regards, mk
Hi,
> 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:
>
> SELECT *
> FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON
> hosts.id = reservation_hosts_1.host_id
>
> LEFT OUTER JOIN
> reservation
> ON
> reservation.id = reservation_hosts_1.reservation_id
>
> INNER JOIN
> (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN
> reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date <=
> 2009-04-10 AND r.start_date < 2009-04-09) OR (r.start_date = 2009-04-09
> AND r.end_date <= 2009-04-10) OR r.start_date > 2009-04-09 )) GROUP BY
> rh.host_id) AS min_date(host_id, start_date)
> ON
> hosts.id = min_date.host_id AND reservation.start_date =
> min_date.start_date
>
> ORDER BY hosts.id, reservation.start_date
>
> Great. But I need to add to this table *hosts which have no reservations
> at all* as well.
>
> If I change INNER JOIN to LEFT OUTER JOIN, it adds the hosts I need, but
> it also lists every reservation, not just those from the subquery.
Do you need a MIN(start_date) for each host you get from the query
before last join?
I think you can solve this with sub-select like this:
select
hosts.*, reservation_hosts.*, reservation.*,
(select MIN(r.start_date) FROM reservation AS r
INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id
where rh.host_id = hosts.id and /*date condition here*/)
FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON
hosts.id = reservation_hosts_1.host_id
LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts_1.reservation_id
ORDER BY hosts.id, reservation.start_date
Note: sub-select must return exactly one row!
--
Aurimas
Hello Aurimas,
Thanks for answer!
> Do you need a MIN(start_date) for each host you get from the query
> before last join?
Yes, I really do - the idea is that from several reservations fulfilling
the dates condition the earliest reservation has to be selected (i.e.
the one with minimum start date).
I edited your code slightly to allow for changed column names and
missing 'hosts' table in the subquery (there were syntax errors otherwise):
select
hosts.*, reservation_hosts.*, reservation.*,
(select MIN(r.start_date) FROM hosts, reservation AS r
INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id
where rh.host_id = hosts.id )
FROM hosts LEFT OUTER JOIN reservation_hosts ON
hosts.id = reservation_hosts.host_id
LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts.reservation_id
ORDER BY hosts.id, reservation.start_date
But it still doesn't work, i.e. it produces every host/reservation
combination (on top of listing hosts with no reservations and NULL in
place of reservation_id, which is fine).
I checked that subquery does indeed return exactly one row, although I'm
not sure why this has meaning.
Regards,
mk
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". 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. -- Sam http://samason.me.uk/
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
On Thu, Apr 09, 2009 at 06:08:04PM +0200, Marcin Krol wrote:
> 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
I think the following should do what you want.
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS (${window_start},${window_end})
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS (${requested_start},${requested_end})
ORDER BY h.id, r.start_date)
The formatting is somewhat grim, but I think it should do what you want.
--
Sam http://samason.me.uk/
Hi, >> Do you need a MIN(start_date) for each host you get from the query >> before last join? > > Yes, I really do - the idea is that from several reservations fulfilling > the dates condition the earliest reservation has to be selected (i.e. > the one with minimum start date). > > I edited your code slightly to allow for changed column names and > missing 'hosts' table in the subquery (there were syntax errors otherwise): The hosts table was not missing in the subquery! It meant to take host-id from "current row: of main select. That subquery should work alone only by replacing host.id by constant value. > select > hosts.*, reservation_hosts.*, reservation.*, > (select MIN(r.start_date) FROM hosts, reservation AS r > INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id > where rh.host_id = hosts.id ) > FROM hosts LEFT OUTER JOIN reservation_hosts ON > hosts.id = reservation_hosts.host_id > > LEFT OUTER JOIN > reservation > ON > reservation.id = reservation_hosts.reservation_id > ORDER BY hosts.id, reservation.start_date > > But it still doesn't work, i.e. it produces every host/reservation > combination (on top of listing hosts with no reservations and NULL in > place of reservation_id, which is fine). Check the main select without the subquery. Does it return the rows you want? If not - its wrong! If yes, than choose *any* host id from main select's result and write a query, that would return a min(start_date) for *that* host. That query should not need hosts table at all since you have a constant host id. Now just place the second query as subquery into the first one, replacing a constant host id by hosts.id. It should work. > I checked that subquery does indeed return exactly one row, although I'm > not sure why this has meaning. I was a bit wrong. Subquery must return 0 or 1 row, but NOT MORE. I hope it's clear now. -- Aurimas
Hello Sam,
Thanks a million for reply! I'm so frustrated with this..
Sam Mason wrote:
> On Thu, Apr 09, 2009 at 06:08:04PM +0200, Marcin Krol wrote:
>> 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
>
> I think the following should do what you want.
>
> SELECT h.id, r.id, r.start_date, r.end_date
> FROM hosts h
> LEFT JOIN (reservation_hosts m INNER JOIN reservation r
> ON m.reservation_id = r.id
> AND (r.start_date,r.end_date) OVERLAPS (${window_start},${window_end})
> ON h.id = m.host_id
> WHERE h.id NOT IN (
> SELECT m.host_id
> FROM reservation r, reservation_hosts m
> WHERE r.id = m.reservation_id
> AND m.host_id IS NOT NULL
> AND (r.start_date,r.end_date) OVERLAPS (${requested_start},${requested_end})
> ORDER BY h.id, r.start_date)
>
> The formatting is somewhat grim, but I think it should do what you want.
Well it almost works: I see that it selects out the host ids whose date
conditions are not met (while adding those that have no reservations),
but why it produces nothing but NULLs in place of values, even for hosts
who do have reservations but ones ?!
id id start_date end_date
4
NULL NULL NULL
5
NULL NULL NULL
6
NULL NULL NULL
7
NULL NULL NULL
8
NULL NULL NULL
9
NULL NULL NULL
10
NULL NULL NULL
11
NULL NULL NULL
12
NULL NULL NULL
13
NULL NULL NULL
I had to edit it a bit: it seems there was one parentheses missing after
first subquery:
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id AND r.start_date > 2009-04-09)
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND r.start_date > 2009-04-09
ORDER BY h.id, r.start_date)
Two things:
- If I quote date values like '2009-04-09' it doesn't work again! I.e.
result set includes one host id that should have been excluded (bc it
has reservation whose date doesn't match the condition)
- I have replaced OVERLAPS with explicit date condition bc PG complained:
ERROR: function pg_catalog.overlaps(date, date, integer, integer) does
not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
On 09/04/2009 18:03, Marcin Krol wrote: > - If I quote date values like '2009-04-09' it doesn't work again! I.e. > result set includes one host id that should have been excluded (bc it > has reservation whose date doesn't match the condition) > > - I have replaced OVERLAPS with explicit date condition bc PG complained: > > ERROR: function pg_catalog.overlaps(date, date, integer, integer) does > not exist > HINT: No function matches the given name and argument types. You may > need to add explicit type casts. I'd imagine this is the same problem as in your other post - if you don't quote the dates, PG thinks each is an integer expression. The error says it's looking for an OVERLAPS function that takes two dates and two integers, which of course doesn't exist. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On Thu, Apr 09, 2009 at 07:03:55PM +0200, Marcin Krol wrote: > - If I quote date values like '2009-04-09' it doesn't work again! I.e. > result set includes one host id that should have been excluded (bc it > has reservation whose date doesn't match the condition) You *need* those quotes in there; you need to figure out what's going on there first before going any further. Maybe the reservation dates for that entry are "confused" for some reason, or you've got the date in the wrong format or something (i.e. you're expecting dd/mm/yyyy and you're getting mmm/dd/yyyy or something else). > - I have replaced OVERLAPS with explicit date condition bc PG complained: > > ERROR: function pg_catalog.overlaps(date, date, integer, integer) does not exist > HINT: No function matches the given name and argument types. You may need to add explicit type casts. This is a big hint that things are going wrong. You need those quotes in there, an "integer" is a plain number and not a date. -- Sam http://samason.me.uk/
Sam Mason wrote:
> This is a big hint that things are going wrong. You need those quotes
> in there, an "integer" is a plain number and not a date.
>
This one does work in the sense of selecting out the wrong host but it
still produces nothing but NULLs!
SELECT h.id, r.id, r.start_date, r.end_date
FROM hosts h
LEFT JOIN (reservation_hosts m INNER JOIN reservation r
ON m.reservation_id = r.id
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date))
ON h.id = m.host_id
WHERE h.id NOT IN (
SELECT m.host_id
FROM reservation r, reservation_hosts m
WHERE r.id = m.reservation_id
AND m.host_id IS NOT NULL
AND (r.start_date,r.end_date) OVERLAPS
('2009-04-09'::date,'2009-04-10'::date)
ORDER BY h.id, r.start_date)
Regards,
mk
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é
> 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.
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@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.
On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
> Sam Mason wrote:
> >This is a big hint that things are going wrong. You need those quotes
> >in there, an "integer" is a plain number and not a date.
>
> This one does work in the sense of selecting out the wrong host but it
> still produces nothing but NULLs!
Yes, it would do.
> SELECT h.id, r.id, r.start_date, r.end_date
> FROM hosts h
> LEFT JOIN (reservation_hosts m INNER JOIN reservation r
> ON m.reservation_id = r.id
> AND (r.start_date,r.end_date) OVERLAPS ('2009-04-09'::date,'2009-04-10'::date))
The dates here are the date range that you want to give to the user; I
was assuming that just because somebody doesn't have a reservation at
the moment you still don't want to put reservations going backwards and
forwards to infinity.
> ON h.id = m.host_id
> WHERE h.id NOT IN (
> SELECT m.host_id
> FROM reservation r, reservation_hosts m
> WHERE r.id = m.reservation_id
> AND m.host_id IS NOT NULL
> AND (r.start_date,r.end_date) OVERLAPS ('2009-04-09'::date,'2009-04-10'::date)
> ORDER BY h.id, r.start_date)
these dates are OK.
As a minor point, you shouldn't need to put the "::date" in unless
you're feeling pedantic, PG should figure that out for itself. I put
them in if I'm unsure of what's going on but most of my queries won't
have them in.
--
Sam http://samason.me.uk/
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>
Sam Mason wrote:
> On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
>> Sam Mason wrote:
>>> This is a big hint that things are going wrong. You need those quotes
>>> in there, an "integer" is a plain number and not a date.
>> This one does work in the sense of selecting out the wrong host but it
>> still produces nothing but NULLs!
>
> Yes, it would do.
Well it does for selecting hosts, but I also want to select the nearest
reservation using r.id like you specified in 'SELECT h.id, r.id,
r.start_date, r.end_date'. I can't do this if r.id is NULL.
>> SELECT h.id, r.id, r.start_date, r.end_date
>> FROM hosts h
>> LEFT JOIN (reservation_hosts m INNER JOIN reservation r
>> ON m.reservation_id = r.id
>> AND (r.start_date,r.end_date) OVERLAPS ('2009-04-09'::date,'2009-04-10'::date))
> The dates here are the date range that you want to give to the user; I
> was assuming that just because somebody doesn't have a reservation at
> the moment you still don't want to put reservations going backwards and
> forwards to infinity.
Not backwards, but forward into some reasonable range, like 3 months (I
want the user to see the nearby reservation in future).
>> ON h.id = m.host_id
>> WHERE h.id NOT IN (
>> SELECT m.host_id
>> FROM reservation r, reservation_hosts m
>> WHERE r.id = m.reservation_id
>> AND m.host_id IS NOT NULL
>> AND (r.start_date,r.end_date) OVERLAPS ('2009-04-09'::date,'2009-04-10'::date)
>> ORDER BY h.id, r.start_date)
>
> these dates are OK.
> As a minor point, you shouldn't need to put the "::date" in unless
> you're feeling pedantic, PG should figure that out for itself. I put
> them in if I'm unsure of what's going on but most of my queries won't
> have them in.
Oops! My PG (ver 8.1) does need this ::date suffix!
Regards,
mk
On Thu, Apr 09, 2009 at 08:16:55PM +0200, Marcin Krol wrote:
> Sam Mason wrote:
> >On Thu, Apr 09, 2009 at 07:25:42PM +0200, Marcin Krol wrote:
> >>SELECT h.id, r.id, r.start_date, r.end_date
> >> FROM hosts h
> >> LEFT JOIN (reservation_hosts m INNER JOIN reservation r
> >> ON m.reservation_id = r.id
> >> AND (r.start_date,r.end_date) OVERLAPS
> >> ('2009-04-09'::date,'2009-04-10'::date))
>
> >The dates here are the date range that you want to give to the user; I
> >was assuming that just because somebody doesn't have a reservation at
> >the moment you still don't want to put reservations going backwards and
> >forwards to infinity.
>
> Not backwards, but forward into some reasonable range, like 3 months (I
> want the user to see the nearby reservation in future).
I'm not sure if this is a question or something else. If you're
wondering how to do this just use a range of ('2009-01-01'::date,
'2009-12-31'::date) to show all entries for this year.
--
Sam http://samason.me.uk/
Thanks a lot, Sam (and others), thanks to your help I managed to finally produce the query I wanted. Regards, mk