MS SQL - Replacing ID values in a query with values from another table

Question

Hopefully I have this explained correctly. This is all new for me.

I have two tables that I'm trying to get the data from. One is dbo.geographic that has two columns, map_id and map_name. These two columns contain the country, state, and city levels for data.

The other table is dbo.orders. The columns I'm interested in are country_id, state_id, and city_id. These columns just have their corresponding numbers from the dbo.geographic table.

I'm trying to run a query that takes the country, state, and city values from dbo.order table and replaces them with correct string from the map_name column of dbo.geographic. Like I said, I'm new to messing with SQL, and I was hoping someone could tell me what I'm missing here (I'm sure it's a simple fix). I've been able to get this to work when it's one column to one column, but it hasn't worked when I tried to get it to go 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 with geographic three times, under different aliases, matching the appropriate column from the orders table with the map_id