Received: from stan.ringways.co.uk ([213.38.87.130]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6KANda13172 for ; Fri, 20 Jul 2001 06:23:39 -0400 (EDT) (envelope-from gary.stainburn@ringways.co.uk) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with smtp (Exim 3.13 #1) id 15NXEX-0006Ss-00 for pgsql-sql@postgresql.org; Fri, 20 Jul 2001 11:10:09 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: "pgsql-sql" Subject: example of [outer] join Date: Fri, 20 Jul 2001 11:22:16 +0100 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01072011221604.16118@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/267 X-Sequence-Number: 4064 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