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
