X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2E9EA3A4324 for ; Wed, 24 Nov 2004 06:32:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33066-07 for ; Wed, 24 Nov 2004 06:32:48 +0000 (GMT) Received: from leda.steelsun.com (dsl093-240-204.ral1.dsl.speakeasy.net [66.93.240.204]) by svr1.postgresql.org (Postfix) with ESMTP id 07A693A4320 for ; Wed, 24 Nov 2004 06:32:45 +0000 (GMT) Received: from leda.steelsun.com (localhost [127.0.0.1]) by leda.steelsun.com (8.12.11/8.12.8) with ESMTP id iAO6WbEU020192; Wed, 24 Nov 2004 01:32:37 -0500 Received: from localhost (cfowler@localhost) by leda.steelsun.com (8.12.11/8.12.11/Submit) with ESMTP id iAO6Wb5j020189; Wed, 24 Nov 2004 01:32:37 -0500 X-Authentication-Warning: leda.steelsun.com: cfowler owned process doing -bs Date: Wed, 24 Nov 2004 01:32:37 -0500 (EST) From: Christian Fowler X-X-Sender: cfowler@leda.steelsun.com To: "Roderick A. Anderson" Cc: Jaime Casanova , pgsql-admin@postgresql.org Subject: Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... In-Reply-To: <41A41CFD.1020109@mailporter.net> Message-ID: References: <20041124050641.86985.qmail@web50004.mail.yahoo.com> <41A41CFD.1020109@mailporter.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/181 X-Sequence-Number: 15564 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