How to get or join data from another table with given id from 2 colums-CodePudding

For this case, I have three tables using mysql in PHPMyAdmin. The first one is Provice, The second one is City, and the third is User.

Province table:
id   name
--------------      
 1   province1      
 2   province2      
 3   province3   

City table:
id   name  
------------    
 1   city1      
 2   city2      
 3   city3 

User table:
id   name      addressB      cityA  provinceA   addressB        cityB   provinceB
----------------------------------------------------------------------------------
 1   Adam      Mango street    1        1       banana street     2         1
 2   Antonio   Peach street    3        2       banana street     2         1

My question is: how can i join the province and city table using query to make result like this:

id   name      addressA      cityA  provinceA   addressB        cityB   provinceB
-------------------------------------------------------------------------------------
 1   Adam      Mango street  city1  province1   banana street   city2   province1
 2   Antonio   Peach street  city3  province2   Guava street    city2   province1

Should I split the CityA, CityB, ProvinceA and ProvinceB from the User table to an independent table? Or there is a or some queries that i've never thought of before?

CodePudding user response：

Query needs to join a referencing table once for each column

select
  a.*, ca.name cityA, cb.name cityB, pa.name provinceA, pb.name provinceB
from user_table a
join city_table ca on a.cityA = ca.id
join city_table cb on a.cityB = cb.id
join province_table pa on a.provinceA = pa.id
join province_table pb on a.provinceB = pb.id
;