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.stainburn@ringways.co.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@postgresql.org so that your
> message can get through to the mailing list cleanly