Re: [GENERAL] complicated query (newbie..)
Thanks a lot, Sam (and others), thanks to your help I managed to finally produce the query I wanted. 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
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
Re: [GENERAL] complicated query (newbie..)
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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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
NULLNULLNULL
5
NULLNULLNULL
6
NULLNULLNULL
7
NULLNULLNULL
8
NULLNULLNULL
9
NULLNULLNULL
10
NULLNULLNULL
11
NULLNULLNULL
12
NULLNULLNULL
13
NULLNULLNULL
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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 r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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/ and you're getting mmm/dd/ 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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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. 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: mrk...@gmail.com To: s...@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. http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009
Re: [GENERAL] complicated query (newbie..)
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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] complicated query (newbie..)
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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
