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 436D03A42C6 for ; Wed, 24 Nov 2004 05:33:00 +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 94460-06 for ; Wed, 24 Nov 2004 05:32:57 +0000 (GMT) Received: from mail.mailporter.net (mail.mailporter.net [66.193.36.114]) by svr1.postgresql.org (Postfix) with ESMTP id ADD793A41E0 for ; Wed, 24 Nov 2004 05:32:56 +0000 (GMT) Received: from mailporter.net [66.162.143.58] by mail.mailporter.net with ESMTP (SMTPD32-8.13) id AD065B301B8; Tue, 23 Nov 2004 21:32:54 -0800 Message-ID: <41A41CFD.1020109@mailporter.net> Date: Tue, 23 Nov 2004 21:32:45 -0800 From: "Roderick A. Anderson" User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040602 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jaime Casanova Cc: pgsql-admin@postgresql.org Subject: Re: joins INNER, OUTER, LEFT, RIGHT, FULL, ... References: <20041124050641.86985.qmail@web50004.mail.yahoo.com> In-Reply-To: <20041124050641.86985.qmail@web50004.mail.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Declude-Sender: raa@mailporter.net [66.162.143.58] X-Note: This E-mail was scanned by Declude JunkMail (www.declude.com) for spam. 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/180 X-Sequence-Number: 15563 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. Rod -- --- [This E-mail scanned for viruses by Declude Virus]