On Tue, 23 Nov 2004, Roderick A. Anderson wrote:
Jaime Casanova wrote:
http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html#QUERIES-JOIN
Been there, done that. Bought several tee-shirts.
What do you mean with *complex joins*?
SELECT first, last, username || '@' || dom.domain as emailaddress FROM cust_main cm LEFT OUTER JOIN cust_pkg cp ON ( cm.custnum = cp.custnum ) LEFT OUTER JOIN cust_svc cs ON ( cp.pkgnum = cs.pkgnum ) LEFT OUTER JOIN svc_email em ON ( cs.svcnum = em.svcnum ) LEFT OUTER JOIN svc_domain dom ON ( em.domain = dom.svcnum ) WHERE zip = '99999' AND username || '@' || dom.domain != '@'
Which worked until I added one more table with a one-to-one relation to cust_main.custnum. Then I got several tuples (2+) for each row above.
try:
SELECT first, last, username || '@' || dom.domain as emailaddress FROM cust_main cm INNER JOIN one_more om ON ( om.custnum = cm.custnum ) LEFT OUTER JOIN cust_pkg cp ON ( cm.custnum = cp.custnum ) LEFT OUTER JOIN cust_svc cs ON ( cp.pkgnum = cs.pkgnum ) LEFT OUTER JOIN svc_email em ON ( cs.svcnum = em.svcnum ) LEFT OUTER JOIN svc_domain dom ON ( em.domain = dom.svcnum ) WHERE zip = '99999' AND username || '@' || dom.domain != '@'
or
SELECT first, last, username || '@' || dom.domain as emailaddress FROM one_more om, cust_main cm LEFT OUTER JOIN cust_pkg cp ON ( cm.custnum = cp.custnum ) LEFT OUTER JOIN cust_svc cs ON ( cp.pkgnum = cs.pkgnum ) LEFT OUTER JOIN svc_email em ON ( cs.svcnum = em.svcnum ) LEFT OUTER JOIN svc_domain dom ON ( em.domain = dom.svcnum ) WHERE zip = '99999' AND om.custnum=cm.custnum AND username || '@' || dom.domain != '@'
The explicit join syntax was freaky for me too at first, but after several years, I prefer it now, since you can easily control your left outer joins
[ \ / [ >X< Christian Fowler | spider AT viovio.com [ / \ http://www.viovio.com | http://www.tikipro.org
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
