CODEWITHSUNDEEP
sql
Salih Erikci
Salih Erikci

Reputation: 5087

Getting a value from another table for multiple columns

I have a table City with two fields, CityCode and RegionCode I have another table Code with two fields Code and CodeMeaning

I want to write a select statement that will display both cityCode, regionCode and their meanings for each city.

If i had to get one meaning from Code table i could do it with join but i don't know how to get values for both columns.

City Table Data
------------------------
CityCode     RegionCode
34             53
41             43

Code Table Data
-----------------
Code        Meaning
34          New York
41          Boston
53          North
43          South


Desired Output
------------------
CityCode     RegionCode    Region   City
34             53          North    New York
41             43          South    Boston

Upvotes: 0

Views: 65

Answers (2)

Z .
Z .

Reputation: 12837

this is a bit poor db design but you can get the data with 2 joins to the codes table:

select c.*, c1.data as city, c2.data as region
from city_table c
join code_table c1 on c1.code = c.city_code
join code_table c2 on c2.code = c.region_code

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use two joins:

select cc.meaning as city, cr.meaning as region
from city c left join
     code cc
     on c.citycode = cc.code left join
     code cr
     on c.regioncode = cr.code

Upvotes: 2

Related Questions