如何编写SQL查询关联location_relation与location_name表并同行展示结果？

需求说明 #

需要关联location_relation和location_name两张表，将每个地点的ID和对应名称按指定格式展示在同一行中。

样本输入数据 #

location_relation表 #

location_name表 #

预期结果 #

测试数据SQL #

create table location_relation
(
    id int,  
    location int, 
    location_sub int
);

create table location_name
(
    id int, 
    location varchar(100)
);

insert into location_relation 
values (1, 400, 401), (2, 500, 501), (3, 600, 601), (4, 700, 701);

insert into location_name 
values (400, 'South Africa'), (401, 'France'),
       (500, 'Germany'), (501, 'EUA'), 
       (600, 'Columbia'), (601, 'Spain'), 
       (700, 'Portugal'), (701, 'Argentina');

尝试的查询（不符合需求） #

select * 
from location_relation lr 
inner join location_name ln on lr.location  = ln.id

union

select * 
from location_relation lr 
inner join location_name ln on lr.location_sub  = ln.id

正确解决方案 #

你用UNION的方式会把两次关联的结果按行合并，无法实现同一行展示ID和名称的需求。正确的做法是两次关联location_name表：一次关联主地点location字段获取对应名称，另一次关联子地点location_sub字段获取对应名称，然后按需选择列并给重复列名设置别名。

正确SQL查询 #

select 
    lr.location,
    lr.location_sub,
    ln_main.location as location_name,
    ln_sub.location as location_sub_name
from location_relation lr
inner join location_name ln_main on lr.location = ln_main.id
inner join location_name ln_sub on lr.location_sub = ln_sub.id;

说明 #

• ln_main是主地点名称表的别名，通过lr.location = ln_main.id关联获取主地点的名称；
• ln_sub是子地点名称表的别名，通过lr.location_sub = ln_sub.id关联获取子地点的名称；
• 如果需要和预期结果的列名完全一致，可调整为：

select 
    lr.location,
    lr.location_sub,
    ln_main.location as location,
    ln_sub.location as location_sub
from location_relation lr
inner join location_name ln_main on lr.location = ln_main.id
inner join location_name ln_sub on lr.location_sub = ln_sub.id;

内容的提问来源于stack exchange，提问作者Sabd