Oracle SQL关联查询问题：映射双位置ID至对应Display Name

你当前的查询只通过to_locn关联了一次Locations表，导致From Location列错误地使用了To Location ID对应的显示名称，完全没匹配From Location ID对应的记录。

需要两次关联Locations表，分别给表起不同别名，用来匹配From Location ID和To Location ID：

select 
    activity_tracking.from_locn as "From Location ID",
    coalesce(loc_from.display_name, '') as "From Location",
    activity_tracking.to_locn as "To Location ID",
    coalesce(loc_to.display_name, '') as "To Location"
from activity_tracking
left join locations loc_from 
    on activity_tracking.from_locn = loc_from.location_id
left join locations loc_to 
    on activity_tracking.to_locn = loc_to.location_id

• 给两次关联的Locations表分别起别名loc_from和loc_to，区分是用来匹配起点还是终点位置
• 使用coalesce()函数替代case语句，更简洁地处理空值场景（当ID为空时返回空字符串）
• 用left join替代full join，确保activity_tracking的所有记录都能被保留，即使对应位置ID在Locations表中不存在

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