title: How to correctly join multiple tables on non-primary key?
body: <p>I'm trying to create a view where the user sees one line per "Batch" joined so that when the "Batch" from the different tables match - then they should go together as one line. But if either table on itself has the "Batch" it should also be added to the result as a row with "NULL" in the other columns.</p>
  
  <p>I think the problem is with how I join the tables. But I can't quite figure out the problem.</p>
  
  <pre><code>CREATE TABLE #ItemTable ([Item] nvarchar(16))
  CREATE TABLE #LocationTable ([Item] nvarchar(16), [Batch] nvarchar(32), [Location] nvarchar(13), [Quantity] int)
  CREATE TABLE #OrderTable ([Item] nvarchar(16), [Batch] nvarchar(32), [Quantity] int)
  CREATE TABLE #BookingTable ([Item] nvarchar(16), [Batch] nvarchar(32), [Quantity] int)
  
  --------------------------------------------------------------------------------------------------
  -- CURRENT RESULT:
  --------------------------------------------------------------------------------------------------
  --  Item    Batch   Location        QuantityOnLocation  OrderedQuantity BookedQuantity
  --  1000    1       Location_1      10                  NULL            NULL
  --  1000    22      Location_2      10                  NULL            NULL
  --  2000    333     Location_3      0                   10              NULL
  --  2000    4444    Location_4      10                  NULL            NULL
  --  3000    666666  NULL            NULL                10              10
  
  --------------------------------------------------------------------------------------------------
  -- DESIRED RESULT:
  --------------------------------------------------------------------------------------------------
  --  Item    Batch   Location        QuantityOnLocation  OrderedQuantity BookedQuantity
  --  1000    1       Location_1      10                  NULL            10
  --  1000    22      Location_2      10                  NULL            0
  --  1000    55555   NULL            NULL                NULL            10
  --  2000    333     Location_3      0                   10              NULL
  --  2000    4444    Location_4      10                  NULL            NULL
  --  3000    666666  NULL            NULL                10              10
  
  
  INSERT INTO #ItemTable ([Item]) VALUES 
  ('1000'), 
  ('2000'), 
  ('3000')
  
  INSERT INTO #LocationTable ([Item], [Batch], [Location], [Quantity]) VALUES 
  ('1000', '1', 'Location_1', 10), 
  ('1000', '22', 'Location_2', 10), 
  ('2000', '333', 'Location_3', 0),
  ('2000', '4444', 'Location_4', 10)
  
  INSERT INTO #OrderTable ([Item], [Batch], [Quantity]) VALUES 
  ('2000', '333', 10), 
  ('3000', '666666', 10)
  
  INSERT INTO #BookingTable ([Item], [Batch], [Quantity]) VALUES 
  ('1000', '1', 10), 
  ('1000', '55555', 10), 
  ('3000', '666666', 10)
  
  
  SELECT 
      [Item].[Item] AS [Item], 
      COALESCE([Location].[Batch], [Order].[Batch], [Booking].[Batch]) AS [Batch],
      [Location].[Location] AS [Location], 
      [Location].[Quantity] AS [QuantityOnLocation],
      [Order].[Quantity] AS [OrderedQuantity],
      [Booking].Quantity AS [BookedQuantity]
  FROM 
      #ItemTable AS [Item]
      LEFT OUTER JOIN (
          SELECT [Item], [Quantity], [Batch], [Location]
          FROM #LocationTable)
      AS [Location] ON [Location].[Item] = [Item].[Item] 
  
      LEFT OUTER JOIN (
          SELECT [Item], [Quantity], [Batch]
          FROM #OrderTable) 
      AS [Order] ON [Order].[Item] = [Item].[Item] 
          AND ISNULL([Order].[Batch], '') = ISNULL([Location].[Batch], [Order].[Batch]) 
  
      LEFT OUTER JOIN (
          SELECT [Item], [Quantity], [Batch]
          FROM #BookingTable) 
      AS [Booking] ON [Order].[Item] = [Item].[Item]
          AND ISNULL([Booking].[Batch], '') = COALESCE([Location].[Batch], [Order].[Batch], [Booking].[Batch]) 
  WHERE
      ISNULL([Location].[Quantity], 0) &lt;&gt; 0
      OR ISNULL([Order].[Quantity], 0) &lt;&gt; 0
      OR ISNULL([Booking].Quantity, 0) &lt;&gt; 0
  
  
  DROP TABLE #ItemTable
  DROP TABLE #LocationTable
  DROP TABLE #BookingTable 
  DROP TABLE #OrderTable
  </code></pre>