Query With Multiple Joins to Same Table

[rickj65]

Looking for a best practice answer for this example:

Let's say I have a main table that has 10 fields that can hold the value of a state code. For example, birth_state, current_residence_state, hs_education_state, etc. (this example is just used for ease of explanation). And the database has a corresponding state code lookup table with the state_cd and state_name.

What is the most efficient way to write a query such that I'm pulling lookup values (e.g. state_name) from the state table for each of those 10 state code fields for all the records in the main table?

Obviously I could join the state table to the main table 10 times (with 10 different aliases) for each state code field, but I'm wondering if there is a more efficient way to write this query.

TIA,

Rick

 

[ESquared]

Do it in the front end. Grab the lookup table once, then use it for all the values. For an example, think of a bound combobox in a form where the bound first column is hidden.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]