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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
