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