Thread: Help with inner/outer join
I'm not sure if somebody would be able to help me with this, but I'm stuck. I've read the SELECT documentation on this but still can't seem to get it to work.
I have a 226 rows in a table called ebs_webserver_t. I run this query that makes a SELECT and JOINs together 6 tables. When I run the query, it only returns 144 web servers. So I need to do a inner or outer join I guess on the ebs_webserver_t. Would anybody be able to help me with the syntax?
Here's the SQL Statement that brings back on 144 rows:
SELECT
ebs_webserver_t.server_instance_nme,
ebs_webserver_t.install_directory,
ebs_webserver_t.docs_directory_nme,
ebs_webserver_t.other_info,
ebs_webserver_t.port_num,
ebs_webserver_t.host_nme,
ebs_webserver_t.ip_num,
ebs_webserver_t.cert_expiration,
ebs_webserver_t.dns_nme,
ebs_webserver_t.url_nme,
ebs_cert_type_t.cert_nme,
ebs_domain_t.domain_nme,
ebs_node_t.node_nme,
ebs_sbu_t.sbu_nme,
ebs_servertypes_t.server_type_nme
FROM
ebs_webserver_t,
ebs_cert_type_t,
ebs_domain_t,
ebs_node_t,
ebs_sbu_t,
ebs_servertypes_t
WHERE ebs_webserver_t.cert_type_id = ebs_cert_type_t.cert_id
AND ebs_webserver_t.domain_id = ebs_domain_t.domain_id
AND ebs_webserver_t.node_id = ebs_node_t.node_id
AND ebs_webserver_t.environment_id = ebs_environment_t.environment_id
AND ebs_webserver_t.sbu_id = ebs_sbu_t.sbu_id
AND ebs_webserver_t.server_type_id = ebs_servertypes_t.server_type_id
Thank you for any help you could give me!
[:==> Troy Campano <==:]
Help with inner/outer joinIt looks like you're trying to join against a table ebs_environment_t that I didn't see in the FROM list, so that might affect things? But anyway, the outer join syntax should look something like this: SELECT ebs_webserver_t.server_instance_nme, ebs_webserver_t.install_directory, ebs_webserver_t.docs_directory_nme, ebs_webserver_t.other_info, ebs_webserver_t.port_num, ebs_webserver_t.host_nme, ebs_webserver_t.ip_num, ebs_webserver_t.cert_expiration, ebs_webserver_t.dns_nme, ebs_webserver_t.url_nme, ebs_cert_type_t.cert_nme, ebs_domain_t.domain_nme, ebs_node_t.node_nme, ebs_sbu_t.sbu_nme, ebs_servertypes_t.server_type_nme FROM ebs_webserver_t LEFT JOIN ebs_cert_type_t ON ebs_webserver_t.cert_type_id = ebs_cert_type_t.cert_id, LEFT JOIN ebs_domain_t ON ebs_webserver_t.domain_id = ebs_domain_t.domain_id, LEFT JOIN ebs_node_t ON ebs_webserver_t.node_id = ebs_node_t.node_id, LEFT JOIN ebs_sbu_t ON ebs_webserver_t.sbu_id = ebs_sbu_t.sbu_id, LEFT JOIN ebs_servertypes_t ON ebs_webserver_t.server_type_id = ebs_servertypes_t.server_type_id WHERE ebs_webserver_t.environment_id = ebs_environment_t.environment_id ----- Original Message ----- From: Troy.Campano@LibertyMutual.com To: pgsql-general@postgresql.org Sent: Monday, December 10, 2001 3:59 PM Subject: [GENERAL] Help with inner/outer join I'm not sure if somebody would be able to help me with this, but I'm stuck. I've read the SELECT documentation on this but still can't seem to get it to work. I have a 226 rows in a table called ebs_webserver_t. I run this query that makes a SELECT and JOINs together 6 tables. When I run the query, it only returns 144 web servers. So I need to do a inner or outer join I guess on the ebs_webserver_t. Would anybody be able to help me with the syntax? Here's the SQL Statement that brings back on 144 rows: SELECT ebs_webserver_t.server_instance_nme, ebs_webserver_t.install_directory, ebs_webserver_t.docs_directory_nme, ebs_webserver_t.other_info, ebs_webserver_t.port_num, ebs_webserver_t.host_nme, ebs_webserver_t.ip_num, ebs_webserver_t.cert_expiration, ebs_webserver_t.dns_nme, ebs_webserver_t.url_nme, ebs_cert_type_t.cert_nme, ebs_domain_t.domain_nme, ebs_node_t.node_nme, ebs_sbu_t.sbu_nme, ebs_servertypes_t.server_type_nme FROM ebs_webserver_t, ebs_cert_type_t, ebs_domain_t, ebs_node_t, ebs_sbu_t, ebs_servertypes_t WHERE ebs_webserver_t.cert_type_id = ebs_cert_type_t.cert_id AND ebs_webserver_t.domain_id = ebs_domain_t.domain_id AND ebs_webserver_t.node_id = ebs_node_t.node_id AND ebs_webserver_t.environment_id = ebs_environment_t.environment_id AND ebs_webserver_t.sbu_id = ebs_sbu_t.sbu_id AND ebs_webserver_t.server_type_id = ebs_servertypes_t.server_type_id Thank you for any help you could give me! [:==> Troy Campano <==:]