pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Eckermann <jeckermann@verio.net>
To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: RE: example of [outer] join
Date: Fri, 20 Jul 2001 11:12:52 -0500
Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B50@NEZU> (raw)

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



view thread (3+ messages)

Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B50@NEZU>
Permalink:  ../08CD1781F85AD4118E0800A0C9B8580B094B50@NEZU/
Also on:    postgresql.org/message-id/08CD1781F85AD4118E0800A0C9B8580B094B50@NEZU

 · 

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: jeckermann@verio.net, gary.stainburn@ringways.co.uk
  Subject: RE: example of [outer] join
  In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B094B50@NEZU>

* 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