RE: example of [outer] join

You could make your FROM clause something like:
FROM members m
INNER JOIN address a ON m.madd = a.aid
INNER JOIN teams t ON m.team = t.tid
LEFT JOIN emails e ON m.memail = e.eid
I think that should work: if not, try putting everything between (but not
including) FROM and LEFT JOIN in brackets (parentheses for those over the
pond :-))

> -----Original Message-----
> From: Gary Stainburn [SMTP:gary(dot)stainburn(at)ringways(dot)co(dot)uk]
> Sent: Friday, July 20, 2001 5:22 AM
> To: pgsql-sql
> Subject: example of [outer] join
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly