From: David Stanaway <david@netventures.com.au>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: example of [outer] join
Date: Fri, 20 Jul 2001 21:12:18 +1000
Message-ID: <E15NYi7-0007iM-00@runt.in.netventures.com.au> (raw)
In-Reply-To: <01072011221604.16118@gary.ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david@netventures.com.au
Subject: Re: example of [outer] join
In-Reply-To: <E15NYi7-0007iM-00@runt.in.netventures.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox