How can I select a primary and secondary row from the same table in Oracle?

by Dennis Konkler
(South Dakota)

I have a table with county codes that has columns of county code and description. Each county has 2 rows. One has county_code as 2 letters and 3 numbers (ie. "AA123") and county_desc as 2 letter state code, a hyphen, and the county name (ie. "AA-Myctyname"). The other is county_code as alpha (ie. "AAMCN") and county_desc (ie. "Myctyname, AA").

I am given the alphanumeric code ("AA123") but I must display the all alpha code ("AAMCN").

What is a simple SELECT statement to use to read the correct all alpha code or what is the easiest way to provide the information required?

Thanks!

If we've understood the question correctly, you have 2 codes for each county, you are given the value of one and want to find the other.

The way to do this is in SQL is to use a subquery - the county code you are given will be your entry point in to the table and will give you the county description. Assuming the county name part of the county_desc field is the same (and that abbreviations are either not used or are used consistently) you can use this to return the associated code you require (the alphabetical code) by chopping up the county_desc and re-arranging the pieces.

Let's suppose we have the following data in our Oracle database table:-

County
-------------------------
county_code | county_desc
-------------------------
SD003         SD-Aurora
SDAUR         Aurora, SD

Therefore, given the code "SD003" we want to return "SDAUR"

In Oracle SQL terms you would write something like this:

SELECT county_code
FROM county
WHERE county_desc =
     (
      SELECT SUBSTR(county_desc,4,LENGTH(county_desc)-3)||
', '||SUBSTR(county_desc,1,2)
      FROM county
      WHERE county_code = :in_county_code
     )

What we'v e done is to chop up the county_desc associated with the given county_code and rearrange it into the form associated with the other county_code.