December 27, 2003 at 10:13 pm
THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"
The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:
[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix
The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:
[StreetSuffix_T]
SuffixID <-----this is the primary key
Name
For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.
THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?
December 28, 2003 at 10:51 am
I got the answer from another forum. It is as follows:
create view ApplicantAddresses
( currstreetnumber
, currstreetname
, ...
, currsuffixname
, prevsuffixname
, empsuffixname
)
as
select currstreetnumber
, currstreetname
, ...
, c.name
, p.name
, e.name
from Applicant_T
inner
join StreetSuffix_T c
on currsuffix = c.SuffixID
inner
join StreetSuffix_T p
on prevsuffix = p.SuffixID
inner
join StreetSuffix_T e
on empsuffix = e.SuffixID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply