complicated query (newbie..) - Mailing list pgsql-general

Hello everyone,

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.


I tried using another LEFT OUTER JOIN with additional query (which is
some arbitrary host selection that will do for the moment) but it
doesn't work:

SELECT hosts.id AS hosts_id, hosts.ip AS hosts_ip, hosts.hostname AS
hosts_hostname, hosts.location AS hosts_location, hosts.architecture_id
AS hosts_architecture_id, hosts.os_kind_id AS hosts_os_kind_id,
hosts.os_version_id AS hosts_os_version_id, hosts.additional_info AS
hosts_additional_info, hosts.column_12 AS hosts_column_12,
hosts.column_13 AS hosts_column_13, hosts.username AS hosts_username,
hosts.password AS hosts_password, hosts.alias AS hosts_alias,
hosts.virtualization_id AS hosts_virtualization_id, hosts.shareable AS
hosts_shareable, hosts.shareable_between_projects AS
hosts_shareable_between_projects, hosts.notes AS hosts_notes, hosts.cpu
AS hosts_cpu, hosts.ram AS hosts_ram, hosts.column_24 AS
hosts_column_24, hosts.batch AS hosts_batch, hosts.asset AS hosts_asset,
hosts.owner AS hosts_owner, hosts.ssh_key_present AS
hosts_ssh_key_present, hosts.machine_type_model AS
hosts_machine_type_model, hosts.mac_address_eth_0 AS
hosts_mac_address_eth_0, hosts.physical_box AS hosts_physical_box,
hosts.up_n_running AS hosts_up_n_running, hosts.available AS
hosts_available, hosts.project_id AS hosts_project_id, reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS
reservation_status, reservation.businessneed AS
reservation_businessneed, reservation.notetohwrep AS
reservation_notetohwrep, reservation.email_id AS reservation_email_id,
reservation.project_id AS reservation_project_id
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

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

LEFT OUTER JOIN
   (SELECT hosts.id FROM hosts WHERE hosts.id IN (10, 11, 12)) AS
nullresv(host_id)
ON
   hosts.id = nullresv.host_id


ORDER BY hosts.id, reservation.start_date


Regards,
mk