Received: from nezu.gs.verio.net (nezu.gs.verio.net [204.27.65.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6KGD1a76699 for ; Fri, 20 Jul 2001 12:13:01 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: by NEZU with Internet Mail Service (5.5.2650.21) id <3G6FXBHQ>; Fri, 20 Jul 2001 11:12:55 -0500 Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B50@NEZU> From: Jeff Eckermann To: "'Gary Stainburn'" , pgsql-sql Subject: RE: example of [outer] join Date: Fri, 20 Jul 2001 11:12:52 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain X-Archive-Number: 200107/280 X-Sequence-Number: 4083 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