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
