Sarah Sarah - 2 months ago 6
SQL Question

Joining two sql tables where one join field is used multiple times

I have an issue linking two tables, where 1 joining field is required multiple times.

The two tables are as follows:

Venue_Location_Master


  • id

  • location_name

  • unid

  • is_warehouse



Bag_Dim


  • event_id

  • Bag_type

  • bag_id

  • label_id

  • created_date

  • created_by_employee

  • origin_location_id

  • destination_location_id

  • composition_id



The tables are joined on origin_location_id or destination_location_id to Venue_Location_Master.id

I'm trying to construct a query that returns:


  • bag_id

  • created_by_employee

  • event_name

  • origin_location_id

  • Venue_Location_Master.location_name (origin_name)

  • destination_location_id

  • Venue_Location_Master.location_name (destination_name)



I've tried using a union, but that returns the required data, but across two rows (see below). Anyone have any suggestions?

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'

Answer

You can join two times on the same table, if you use aliases.

Just join on the right fields, and this should do the trick.

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'