From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: example of [outer] join
Date: Fri, 20 Jul 2001 11:22:16 +0100
Message-ID: <01072011221604.16118@gary.ringways.co.uk> (raw)
Hi, me again.
I'm having fun here, but here's another question for you.
Could someone please give me an example of a join where one of the fields is
missing - I think reading from the docs that this is an OUTER join, but I'm
having a problem with the syntax. I'm trying to create a view that pulls in
all the relevent details for a single member.
I'm still with my members table, which has links to the address table (m.madd
= a.aid), teams table (m.mteam = t.tid) and the email table (m.memail =
e.eid).
While every member has an address, and every member belongs to a team, not
everyone has an email address. My problem is that every member without an
email address gets omitted from the result set.
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;
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
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: gary.stainburn@ringways.co.uk
Subject: Re: example of [outer] join
In-Reply-To: <01072011221604.16118@gary.ringways.co.uk>
* 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