Re: 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