Displaying 'Looked Up' Value in SQL Select Based on an Index Value

Forgive the somewhat Excel-like description, but I am teaching myself SQL "on the job" so to speak, and need some help with what I suspect is a bit of a basic issue.

I am joining two tables on a key, and the join is working fine. Each of the two tables has a non-key field called "county_index" that has an integer that refers to a different table that has nothing in it but the county_index and county_name fields in it.

The problem I am having is that I don't know how to display the NAME of the county (which is not stored in either of the joined tables) instead of the county_index value which IS stored in it. The problem is that I have two, separate counties to look up in each joined record, so I haven't been able to add the county table through a join command without messing things up.

See the Code section for an example of the join and the output - basically, instead of the county_index in the output below, I want to display the county_name.

The join is working fine and showing the correct record set, and the county_index result in each of the two county id columns is the correct "index" into a county table that looks like this:

county_index         county_name
43                          Orange County
44                          Blue County
45                          Red County
46                          Purple County

So, given all that, how can I get from displaying the "county_index" in each column, to displaying the associated "county_name"?

Many thanks!
SELECT parcels.file_number, 
       parcels.county_index as 'Parcel_County', 
       units.county_index as 'Unit_County'
FROM   parcels inner join units on 
       parcels.file_number = units.file_number
file_number    Parcel_County    unit_id       Unit_County
101                  43              220            44
101                  43              221            45
101                  43              222            46

Open in new window

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
SELECT p.file_number, c1.county_name as 'Parcel_County', u.unit_id, c2.county_name as 'Unit_County'
FROM counties c1 INNER JOIN
      parcels p ON c1.county_index = p.county_index INNER JOIN
      units u ON p.file_number = u.file_number INNER JOIN
      counties c2 ON u.county_index = c2.county_index
propertytaxAuthor Commented:
Beauty - works perfectly - thanks for the SQL lesson :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.