加入 1 到 N 个映射列 [英] Join On 1 to N mapped columns

问题描述

我这里有两张桌子

EMPLOYEE_DETAILS:

EMPLOYEE_DETAILS:

+---------+-------------+--------------+
| EMP_ID  | AREA_CODE   | SECTOR_CODE  |
+---------+-------------+--------------+
| 1223    | 5001        | 1001         |
| 3224    | 6001        | 2001         |
+---------+-------------+--------------+

LOCATION_DETAILS:

LOCATION_DETAILS:

+------+---------------+
| ID   | LOCATION_NAME |
+------+---------------+
| 1001 | SECTOR 1      |
| 2001 | SECTOR 2      |
| 5001 | AREA 1        |
| 6001 | AREA 2        |
+------+---------------+

列 AREA_CODE &EMPLOYEE_DETAILS 的SECTOR_CODE 逻辑引用LOCATION_DETAILS 表的ID 列.两者之间没有物理外键约束.

Columns AREA_CODE & SECTOR_CODE of EMPLOYEE_DETAILS references column ID of LOCATION_DETAILS table logically. There are no physical foreign key constraints among the 2.

我正在尝试获取 AREA_NAME &每个EMP_ID的SECTOR_NAME.

I'm trying to get the AREA_NAME & SECTOR_NAME of each EMP_ID.

预期输出:

+---------+-------------+--------------+
| EMP_ID  | AREA_NAME   | SECTOR_NAME  |
+---------+-------------+--------------+
| 1223    | AREA 1      | SECTOR 1     |
| 3224    | AREA 2      | SECTOR 2     |
+---------+-------------+--------------+

有没有办法通过连接查询来实现这一点?非常感谢..

Is there any way to achieve this via a join query? Many Thanks..

推荐答案

同一个表可以用不同的别名加入两次

You can join the same table twice with different alias names

select e.emp_id, 
       a.LOCATION_NAME as area,
       s.LOCATION_NAME as sector
from EMPLOYEE_DETAILS e
join LOCATION_DETAILS a on a.id = e.AREA_CODE   
join LOCATION_DETAILS s on s.id = e.SECTOR_CODE  

这篇关于加入 1 到 N 个映射列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！