pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (3+ messages)  latest in thread

Message-ID: <E15NYi7-0007iM-00@runt.in.netventures.com.au>
Permalink:  ../E15NYi7-0007iM-00@runt.in.netventures.com.au/
Also on:    postgresql.org/message-id/E15NYi7-0007iM-00@runt.in.netventures.com.au

 · 

reply

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