如何在mysql中合并3个表

我需要一些帮助

我有3张桌子,看起来像:

user_base_info
+-------+-----------+------------------+------------------+------------------+------------------+
|user_id| real_name |    live_province |   live_city      | live_area_big    | live_area_small  |
+-------+-----------+------------------+------------------+------------------+------------------+
|    1  |  John     |      15000000    |    15300000      |    15310000      |    15310003      |
|    2  |  Susan    |      42000000    |    42140000      |    42140400      |    42140401      |
|    3  |  Andy     |      12000000    |    58300000      |    58302000      |    58302004      |
|    4  |  Knoxvile |      12000000    |    12100000      |    12110000      |    12110002      |
|    5  |  Abraham  |      13000000    |    50200000      |    50200000      |    11115007      |
|  ...  |  ........ |      ...         |    ...           |    ...           |    ...           |
|331508 |  Donald   |      41000000    |    41010000      |    41011200      |    41011202      |
+-------+-----------+------------------+------------------+------------------+------------------+

borrow_progress
+--------+-----------+------------+------------+-------------+
|user_id | borrow_id |  state     |  remark    | create_time |
+--------+-----------+------------+------------+-------------+
|  170   |  236      |  10        |  waiting   |  24/04/17   |   
|  170   |  236      |  22        |  proceed   |  02/02/17   |     
|  170   |  236      |  26        |  success   |  25/04/17   |    
|  170   |  236      |  30        |  sent      |  05/11/17   |   
|  172   |  237      |  40        |  completed |  03/07/17   |   
|  ...   |  ...      |  ...       |  ...       |    ...      |   
| 353252 | 24112     |  90        |  failed    |  30/01/17   |   
+--------+-----------+------------+------------+-------------+

area
+------------+---------------------+
|code        |      name           |
+------------+---------------------+
| 11000000   | Jawa Tengah         |   
| 12000000   | Jawa Barat          |     
| 13000000   | Jawa Timur          |    
| 14000000   | Sumatera Utara      |   
| 15000000   | DKI Jakarta         |   
|  ...       |  ...                |   
| 41000000   | Di Yogyakarta       |  
| 42140000   | Kota Singkawang     | 
| 58300000   | Kota Depok          | 
| 12100000   | Bandung             | 
| 50200000   | Kabupaten Sukoharjo | 
| 41010000   | Kabupaten Sleman    | 
| 15310000   | Cilandak            | 
| 42140400   | Singkawang Barat    | 
| 41011200   | Mlati               | 
| 12110000   | Arjasari            | 
| 15310003   | Gandaria Selatan    | 
| 41011202   | Sinduadi            |   
+------------+---------------------+

我想在表user_base_info中选择一个用户,该表在表roe_progress中具有状态= 90,并更改表名称在表区域中的live_province,live_city,live_area_big,live_area_small代码。

我希望结果看起来像这样:

+-------+-----------+------------------+------------------+------------------+------------------+
|user_id| real_name |    live_province |   live_city      | live_area_big    | live_area_small  |
+-------+-----------+------------------+------------------+------------------+------------------+
|331508 |  Donald   |   Di Yogyakarta  | Kabupaten Sleman |    Mlati         |    Sinduadi      |
+-------+-----------+------------------+------------------+------------------+------------------+

我试过了

SELECT 
    *
FROM
    borrow_progress a
        INNER JOIN
    user_base_info b ON a.user_id = b.user_id
        INNER JOIN
    area c ON c.code = b.live_city
WHERE
    state = 90

但这不是我想要的。

评论