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

Posted on 2009-05-01
Last Modified: 2012-05-06
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

Question by:propertytax
    LVL 92

    Accepted Solution

    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

    Author Closing Comment

    Beauty - works perfectly - thanks for the SQL lesson :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now