From: | "Jackson, DeJuan" <djackson(at)cpsgroup(dot)com> |
---|---|
To: | Andy <alewis(at)mpsi(dot)net> |
Cc: | PGSQL SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | RE: Two table select (fwd) |
Date: | 1999-02-03 20:57:26 |
Message-ID: | F10BB1FAF801D111829B0060971D839F63FBBA@cpsmail |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Perdue | 1999-02-04 02:25:25 | Performance Question |
Previous Message | Jan Wieck | 1999-02-03 19:50:36 | Re: [SQL] Two table select |