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

Posted on 2009-05-01
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 500 total points
ID: 24284523
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

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

862 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