连接两个SQL表，其中一个连接字段被多次使用

debugcn 发表于 Dev

莎拉

我在链接两个表时遇到问题，其中多次需要1个联接字段。

这两个表如下：

地点_地点_主人

ID
地点名称
项目
is_warehouse

Bag_Dim

event_id
Bag_type
bag_id
label_id
创建日期
created_by_employee
origin_location_id
destination_location_id
composition_id

这些表通过origin_location_id或destination_location_id连接到Venue_Location_Master.id

我正在尝试构造一个返回的查询：

bag_id
created_by_employee
event_name
origin_location_id
Venue_Location_Master.location_name（来源名称）
destination_location_id
Venue_Location_Master.location_name（目标名称）

我尝试使用联合，但这会返回所需的数据，但会跨越两行（请参见下文）。有人有什么建议吗？

SELECT [bag_id],
[created_date],
[created_by_employee],
[origin_location_id], 
ISNULL([venue_location_master].[location_name], 'NULL') AS [origin_location_name],   
[destination_location_id],
ISNULL([venue_location_master].[location_name], 'NULL') AS [destination_location_name]    
,ISNULL([event_master].[event_name], 'NULL') AS [event_name]  
FROM [variance_cash].[dbo].[Bag_Dim] 
LEFT JOIN [verteda_rts_v4].[dbo].[venue_location_master]  
ON [Bag_Dim].[destination_location_id] = [venue_location_master].[id]  
LEFT JOIN [verteda_rts_v4].[dbo].[event_master] 
ON [Bag_Dim].[event_id] = [event_master].[id] 
WHERE  [bag_id] = 'K5334'

拉斐尔的老房子

如果使用别名，则可以在同一表上连接两次。

只要加入正确的领域，这应该可以解决问题。

SELECT 
  [bag_id],
  [created_date],
  [created_by_employee],

  --origin
  [origin_location_id],
  --use table alias to get correct origin name
  ISNULL(origin.[location_name], 'NULL') AS [origin_name],  

  --destination
  [destination_location_id],
  --use table alias to get correct destination name
  ISNULL(destination.[location_name], 'NULL') AS [destination_name],

  ISNULL([event_master].[event_name], 'NULL') AS [event_name]  
FROM [variance_cash].[dbo].[Bag_Dim] 

--join on destination, alias is... destination
LEFT JOIN [verteda_rts_v4].[dbo].[venue_location_master]  as destination
      ON [Bag_Dim].[destination_location_id] = destination.[id] 

--join  on origin, alias is... origin
LEFT JOIN [verteda_rts_v4].[dbo].[venue_location_master] as origin  
      ON [Bag_Dim].[origin_location_id] = origin.[id]  

LEFT JOIN [verteda_rts_v4].[dbo].[event_master] 
   ON [Bag_Dim].[event_id] = [event_master].[id] 
WHERE  [bag_id] = 'K5334'

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。