Help writing a query

[ronnie]

Hi,
I have a address table with the following fields

Address1
Address2
City
StateId
Zip
CountryId

and another table region with the following fields

Region_id
Region_Name
Region_Type

The state_id and Country_id in the address table are foreign keys to the region table where the states and countries are stored with a type of state . country.

I want to write a query which will join these 2 tables and display the results as

address1,address2,state,zip , country

Note that i want to display the state and country names and not their Id's, in this case the region_name.

I am having difficulty writing this query.

please help.

Thanks
Ronnie

[sambavan]

        SELECT A.address1,
                    A.address2,
                    A.City
                    B.region_name
                    A.Zip
                    C.region_name
        FROM Address_tbl A,
                 Region_tbl B,
                 Region_tbl C
         WHERE A.state_id = B.region_id
                 AND  A.country_id = C.region_id
                 AND  B.region_type IN ('State')
                 AND  C.region_type IN ('Country'); 

You can refine the query by using the Italic phase lines, if you are sure of their types. Otherwise you can just ignore those lines and run your query.

Sam

[Edited by sambavan on 11-20-2001 at 03:22 PM]