Received: from mailin8.bigpond.com (juicer39.bigpond.com [139.134.6.96]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6KBBTa32636 for ; Fri, 20 Jul 2001 07:11:30 -0400 (EDT) (envelope-from david@netventures.com.au) Received: from runt.in.netventures.com.au ([144.135.24.69]) by mailin8.bigpond.com (Netscape Messaging Server 4.15) with SMTP id GGRRCP00.JK5 for ; Fri, 20 Jul 2001 21:17:13 +1000 Received: from KTPP-p-203-54-3-226.prem.tmns.net.au ([203.54.3.226]) by bwmam01.mailsvc.email.bigpond.com(MailRouter V2.9g 8311/521840); 20 Jul 2001 21:17:13 Received: from ciderbox.in.netventures.com.au ([192.168.10.19] helo=localhost) by runt.in.netventures.com.au with esmtp (Exim 3.03 #1 (Debian)) id 15NYi7-0007iM-00 for ; Fri, 20 Jul 2001 21:44:47 +1000 Date: Fri, 20 Jul 2001 21:12:18 +1000 Content-Type: multipart/alternative; boundary=Apple-Mail-636313059-1 X-Mailer: Apple Mail (2.388) From: David Stanaway To: "pgsql-sql" Mime-Version: 1.0 (Apple Message framework v388) In-Reply-To: <01072011221604.16118@gary.ringways.co.uk> Subject: Re: example of [outer] join Message-Id: X-Archive-Number: 200107/268 X-Sequence-Number: 4065 --Apple-Mail-636313059-1 Content-Transfer-Encoding: 7bit Content-Type: text/plain; format=flowed; charset=us-ascii On Friday, July 20, 2001, at 08:22 PM, Gary Stainburn wrote: My view so far is: CREATE view member_dets as select *, getphone(m.mphone) as phone, getphone(m.mfax) as fax, getphone(m.mmobile) as mobile, getunitno(m.mid) as munitno from members m, address a, teams t, emails e where m.madd = a.aid and m.memail = e.eid and m.mteam = t.tid; Try CREATE VIEW member_dets AS SELECT *, getphone(m.mphone) AS phone, getphone(m.mfax) AS fax, getphone(m.mmobile) AS mobile, getunitno(m.mid) AS munitno FROM members m -- every member has an address JOIN address a ON m.madd = a.aid -- not everyone has an email address LEFT JOIN emails e ON m.memail = e.eid -- every member belongs to a team JOIN team t ON m.mteam = t.tid; You will need postgresql 7.1 for this syntax, otherwise, refer to the docs on how do do outer joins using unions (Pretty messy esp if you have more than one... One very good reason to upgrade to 7.1 if you are still using an earlier version of postgres) ============================== David Stanaway Personal: david@stanaway.net Work: david@netventures.com.au --Apple-Mail-636313059-1 Content-Transfer-Encoding: 7bit Content-Type: text/enriched; charset=us-ascii On Friday, July 20, 2001, at 08:22 PM, Gary Stainburn wrote: 0000,0000,DEDEMy view so far is: CREATE view member_dets as select *, getphone(m.mphone) as phone, getphone(m.mfax) as fax, getphone(m.mmobile) as mobile, getunitno(m.mid) as munitno from members m, address a, teams t, emails e where m.madd = a.aid and m.memail = e.eid and m.mteam = t.tid; Try CREATE VIEW member_dets AS SELECT *, getphone(m.mphone) AS phone, getphone(m.mfax) AS fax, getphone(m.mmobile) AS mobile, getunitno(m.mid) AS munitno FROM members m -- every member has an address JOIN address a ON m.madd = a.aid -- not everyone has an email address LEFT JOIN emails e ON m.memail = e.eid -- every member belongs to a team JOIN team t ON m.mteam = t.tid; You will need postgresql 7.1 for this syntax, otherwise, refer to the docs on how do do outer joins using unions (Pretty messy esp if you have more than one... One very good reason to upgrade to 7.1 if you are still using an earlier version of postgres) ============================== David Stanaway Personal: david@stanaway.net Work: david@netventures.com.au --Apple-Mail-636313059-1--