Hi,
> I've got 3 tables: hosts (with host.id column) and reservation (with
> reservation.id column) in many-to-many relation, and reservation_hosts
> which is an association table (with reservation_id and host_id columns).
>
> So I've got this query which selects hosts and reservations under
> certain conditions:
>
> SELECT *
> FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON
> hosts.id = reservation_hosts_1.host_id
>
> LEFT OUTER JOIN
> reservation
> ON
> reservation.id = reservation_hosts_1.reservation_id
>
> INNER JOIN
> (SELECT rh.host_id, MIN(r.start_date) FROM reservation AS r INNER JOIN
> reservation_hosts AS rh ON (r.id=rh.reservation_id AND ( (r.end_date <=
> 2009-04-10 AND r.start_date < 2009-04-09) OR (r.start_date = 2009-04-09
> AND r.end_date <= 2009-04-10) OR r.start_date > 2009-04-09 )) GROUP BY
> rh.host_id) AS min_date(host_id, start_date)
> ON
> hosts.id = min_date.host_id AND reservation.start_date =
> min_date.start_date
>
> ORDER BY hosts.id, reservation.start_date
>
> Great. But I need to add to this table *hosts which have no reservations
> at all* as well.
>
> If I change INNER JOIN to LEFT OUTER JOIN, it adds the hosts I need, but
> it also lists every reservation, not just those from the subquery.
Do you need a MIN(start_date) for each host you get from the query
before last join?
I think you can solve this with sub-select like this:
select
hosts.*, reservation_hosts.*, reservation.*,
(select MIN(r.start_date) FROM reservation AS r
INNER JOIN reservation_hosts AS rh ON r.id=rh.reservation_id
where rh.host_id = hosts.id and /*date condition here*/)
FROM hosts LEFT OUTER JOIN reservation_hosts AS reservation_hosts_1 ON
hosts.id = reservation_hosts_1.host_id
LEFT OUTER JOIN
reservation
ON
reservation.id = reservation_hosts_1.reservation_id
ORDER BY hosts.id, reservation.start_date
Note: sub-select must return exactly one row!
--
Aurimas