Mysql join tables 并将一个表中的area_id替换为另一个表中的area_name

Question

我希望表 cities 中的 area_id 与其区域名称右连接，区域名称是表 areas 中的 name_en(I 强调行)。

表 1:城市

╔════════════╦═════════╦═══════════════╗
║ COUNTRY_ID ║ AREA_ID ║    NAME_EN    ║
╠════════════╬═════════╬═══════════════╣
║ US         ║       1 ║ Los Angeles   ║
║ US         ║       1 ║ San Francisco ║
║ US         ║       2 ║ Kansas City   ║
╚════════════╩═════════╩═══════════════╝

表 2:区域

╔════╦════════════╗
║ ID ║  NAME_EN   ║
╠════╬════════════╣
║  1 ║ California ║
║  2 ║ Missouri   ║
╚════╩════════════╝

我的查询:

SELECT c.country_id, c.area_id, c.name_en 
FROM `cities` AS c RIGHT JOIN `areas` AS a ON c.area_id = a.name_en 

Answer 1

这里不需要RIGHT JOIN，而是INNER JOIN。

SELECT  a.Country_ID, 
        a.Name_en CountryName,
        b.Name_en AreasName
FROM    Cities a
        INNER JOIN Areas b
            ON a.area_ID = b.ID

• SQLFiddle Demo

要进一步了解有关联接的更多信息，请访问以下链接:

• Visual Representation of SQL Joins

输出

╔════════════╦═══════════════╦════════════╗
║ COUNTRY_ID ║  COUNTRYNAME  ║ AREASNAME  ║
╠════════════╬═══════════════╬════════════╣
║ US         ║ Los Angeles   ║ California ║
║ US         ║ San Francisco ║ California ║
║ US         ║ Kansas City   ║ Missouri   ║
╚════════════╩═══════════════╩════════════╝