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

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