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.unit_id, 
       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

Select allOpen in new window