Normalized Addresses - Need Help

[JabbaTheNut]

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?


Game Over, Man!

 

[r937]

[tt]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[/tt]

rudy

http://r937.com/

 

[JabbaTheNut]

r937,

Excellent! You saved me a lot of frustration.

Thanks

Game Over, Man!