Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6KEfSa31374 for ; Fri, 20 Jul 2001 10:41:28 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 88697; Fri, 20 Jul 2001 07:41:22 -0700 From: "Josh Berkus" Subject: Re: multiple lookup per row To: Gary Stainburn , "pgsql-sql" X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 20 Jul 2001 07:41:22 -0700 Message-ID: In-Reply-To: <01072010391603.16118@gary.ringways.co.uk> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===88697====davinci.ethosmedia.com===_" X-Archive-Number: 200107/274 X-Sequence-Number: 4071 This is a multi-part MIME message --_===88697====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit Gary, First: Go out and buy "SQL for Smarties". Now. Read it. However, I'll give you this one as a freebie: > I've got a table 'phones' which has an indexed key 'pid' of type > int4, and a > phone number of type varchar(12). > > I've then got a table 'members' which as an index key 'mid' of type > int4. SELECT members.mid, members.name, members.address, hp.phone AS home_phone, wp.phone AS work_phone, cp.phone as cell_phone FROM members LEFT OUTER JOIN (SELECT mid, phone FROM phones WHERE ptype = 'home') hp ON members.mid = hp.mid LEFT OUTER JOIN (SELECT mid, phone FROM phones WHERE ptype = 'work') wp ON members.mid = wp.mid LEFT OUTER JOIN (SELECT mid, phone FROM phones WHERE ptype = 'cell') cp ON members.mid = cp.mid ORDER BY members.name; You're experiencing the usual problem encountered by procedural programmers when they first start on SQL. SQL is a declarative language, and requires a different knid of thinking than procedural languages. Thus the use of table aliasing and subselects above. Have fun! -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco --_===88697====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88697====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88697====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===88697====davinci.ethosmedia.com===_--