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
