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

Reply via email to