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
