pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
example of [outer] join
3+ messages / 3 participants
[nested] [flat]

* example of [outer] join
@ 2001-07-20 10:22 Gary Stainburn <gary.stainburn@ringways.co.uk>
  2001-07-20 11:12 ` Re: example of [outer] join David Stanaway <david@netventures.com.au>
  0 siblings, 1 reply; 3+ messages in thread

From: Gary Stainburn @ 2001-07-20 10:22 UTC (permalink / raw)
  To: pgsql-sql

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     



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: example of [outer] join
  2001-07-20 10:22 example of [outer] join Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2001-07-20 11:12 ` David Stanaway <david@netventures.com.au>
  0 siblings, 0 replies; 3+ messages in thread

From: David Stanaway @ 2001-07-20 11:12 UTC (permalink / raw)
  To: pgsql-sql


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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: example of [outer] join
@ 2001-07-20 16:12 Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Jeff Eckermann @ 2001-07-20 16:12 UTC (permalink / raw)
  To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-07-20 16:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-20 10:22 example of [outer] join Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-20 11:12 ` David Stanaway <david@netventures.com.au>
2001-07-20 16:12 RE: example of [outer] join Jeff Eckermann <jeckermann@verio.net>

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