MS SQL - replacing identifier values ​​in a query with values ​​from another table

Question

I hope I have correctly explained this. This is all new to me.

I have two tables from which I am trying to get data. One of them is dbo.geographic , which has two columns: map_id and archive_name . These two columns contain information about the country, state and city.

Another table is dbo.orders . The columns that interest me are: country_id , state_id and city_id . These columns have only the corresponding numbers from the dbo.geographic table.

I am trying to run a query that takes the values ​​of a country, state and city from the dbo.order table and replaces them with the correct row from the map_name of the dbo.geographic column. As I said, I'm new to working with SQL, and I was hoping someone would tell me what I was missing here (I'm sure this is a simple fix). I was able to get this to work when it has one column to one column, but it didn't work when I tried to get it to move from one column to three.

Thanks!

Answer 1

Try something like this:

SELECT country.map_name, state.map_name, city.map_name, orders.other_columns FROM orders JOIN geographic AS country ON country.map_id = orders.country_id JOIN geographic AS state ON state.map_id = orders.state_id JOIN geographic AS city ON city.map_id = orders.city_id 

Essentially, you join geographic three times, under different aliases, matching the corresponding column from the orders table with map_id