如何关联Assets与Transfers表获取指定格式的资产转移数据？

数据表结构 #

Assets表 #

Asset Id | Asset Name | Asset Type | Product Id

Transfers表 #

Transfer Id | From Asset ID | To Asset Id | Product Id

查询需求 #

需要得到如下格式的查询结果：

Transfer ID | From Asset Name | To Asset Name | Product Id

原查询问题 #

此前使用的SQL语句：

Select *
From Transfer
Left join Asset on asset.id = from_asset_id or asset.id = to_asset_id

该语句会将转出、转入资产名称分别显示在不同行中，无法满足同一行展示的需求。

解决方案 #

需要两次关联Assets表，通过给关联表设置别名来区分转出和转入资产的关联逻辑，具体SQL如下：

SELECT
    t.`Transfer Id` AS `Transfer ID`,
    from_asset.`Asset Name` AS `From Asset Name`,
    to_asset.`Asset Name` AS `To Asset Name`,
    t.`Product Id`
FROM Transfers t
LEFT JOIN Assets from_asset 
    ON from_asset.`Asset Id` = t.`From Asset ID`
LEFT JOIN Assets to_asset 
    ON to_asset.`Asset Id` = t.`To Asset Id`;

逻辑说明 #

• 给Transfers表设置别名t，简化语句书写
• 第一次关联Assets表时设置别名from_asset，匹配From Asset ID字段，获取转出资产的名称
• 第二次关联Assets表时设置别名to_asset，匹配To Asset Id字段，获取转入资产的名称
• 使用LEFT JOIN保证即使转出/转入资产ID在Assets表中无对应记录，Transfer的原始数据也会被保留

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