Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA80702 for ; Wed, 3 Feb 1999 15:56:03 -0500 (EST) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id OAA04319; Wed, 3 Feb 1999 14:55:58 -0600 (CST) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (4.1) id xmaa03397; Wed, 3 Feb 99 14:55:09 -0600 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id ; Wed, 3 Feb 1999 14:57:28 -0600 Message-ID: From: "Jackson, DeJuan" To: Andy Cc: PGSQL SQL Subject: RE: Two table select (fwd) Date: Wed, 3 Feb 1999 14:57:26 -0600 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain I thought as much... the best way to do a single select, with what you currently have, would be to use a union all clause. SELECT r.bldg1, ..., a.bldgname FROM refferal r, apts a WHERE r.bldg1 = a.bldgnum UNION ALL SELECT r.bldg2, ..., a.bldgname FROM refferal r, apts a WHERE r.bldg2 = a.bldgnum UNION ALL . . . Now if you normalize your data into three table you could make it a single select (w/o UNION) with a three table join. BTW it's a good idea to place non-variable length fields before variable length ones in a table definition. i.e. table = referral +----------------------------------+----------------------------------+- ------+ | ref_id | int4 | 4 | | rstate | varchar() | 2 | | rrif | varchar() | 10 | | rphone | varchar() | 20 | | rfirstname | varchar() | 20 | | rlastname | varchar() | 25 | +----------------------------------+----------------------------------+- ------+ table = refbldg +----------------------------------+----------------------------------+- ------+ | Field | Type | Length| +----------------------------------+----------------------------------+- ------+ | ref_id | int4 | 4 | | rbldg | varchar() | 10 | | rbldgcomment | text | var | +----------------------------------+----------------------------------+- ------+ table = apts +----------------------------------+----------------------------------+- ------+ | Field | Type | Length| +----------------------------------+----------------------------------+- ------+ | bldgnum | varchar() | 10 | | bldgname | varchar() | 35 | ... +----------------------------------+----------------------------------+- ------+ SELECT r.*, rb.refbldg, rb.rbldgcomment, a.bldgname FROM referral r. refbldg rb, apts a WHERE r.ref_if = rb.ref_if AND rb.rbldg = a.bldgnum