Re: example of [outer] join

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