How to correctly join multiple tables on non-primary key?

Question

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.

I think the problem is with how I join the tables. But I can't quite figure out the problem.

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) <> 0
    OR ISNULL([Order].[Quantity], 0) <> 0
    OR ISNULL([Booking].Quantity, 0) <> 0


DROP TABLE #ItemTable
DROP TABLE #LocationTable
DROP TABLE #BookingTable 
DROP TABLE #OrderTable

Answer 1

You made a typo (I think) on your last join, this bit:

LEFT OUTER JOIN (
    SELECT [Item], [Quantity], [Batch]
    FROM #BookingTable) 
AS [Booking] ON [Order].[Item] = [Item].[Item]

Should that not be:

ON [Booking].[Item] = [Item].[Item]

I rewrote your query slightly to this:

SELECT 
    i.Item AS Item, 
    COALESCE(l.Batch, o.Batch, b.Batch) AS Batch,
    l.Location AS Location, 
    l.Quantity AS QuantityOnLocation,
    o.Quantity AS OrderedQuantity,
    b.Quantity AS BookedQuantity
FROM 
    #ItemTable i
    LEFT JOIN #LocationTable l ON l.Item = i.Item
    LEFT JOIN #OrderTable o ON o.Item = i.Item AND o.Batch = ISNULL(l.Batch, o.Batch) 
    LEFT JOIN #BookingTable b ON b.Item = i.Item AND b.Batch = COALESCE(l.Batch, o.Batch, b.Batch) 
WHERE
    ISNULL(l.Quantity, 0) != 0
    OR ISNULL(o.Quantity, 0) != 0
    OR ISNULL(b.Quantity, 0) != 0;

Which seems more readable to me, but I guess this is personal preference?

Then I realised that this still doesn't give you what you want, so I refactored it again to get this (which does give you the desired results):

WITH UniqueItemBatch AS (
    SELECT DISTINCT Item, Batch FROM #LocationTable
    UNION
    SELECT DISTINCT Item, Batch FROM #OrderTable
    UNION
    SELECT DISTINCT Item, Batch FROM #BookingTable)
SELECT 
    u.Item AS Item, 
    u.Batch,
    l.Location AS Location, 
    l.Quantity AS QuantityOnLocation,
    o.Quantity AS OrderedQuantity,
    b.Quantity AS BookedQuantity
FROM
    UniqueItemBatch u
    LEFT JOIN #ItemTable i ON i.Item = u.Item
    LEFT JOIN #LocationTable l ON l.Item = u.Item AND l.Batch = u.Batch
    LEFT JOIN #OrderTable o ON o.Item = u.Item AND o.Batch = u.Batch
    LEFT JOIN #BookingTable b ON b.Item = u.Item AND b.Batch = u.Batch
WHERE
    ISNULL(l.Quantity, 0) != 0
    OR ISNULL(o.Quantity, 0) != 0
    OR ISNULL(b.Quantity, 0) != 0;

Answer 2

I'm not sure as to the logic for your final column, but this gives the desired results for the other columns.

To get your query based on either bookings or locations showing as a batch, I have unioned the two tables together in the query.

I would suggest, if possible, revisiting the design of your data structure

select 
    item.Item,
    batch.Batch, 
    max(batch.location) as location,
    sum(batch.LQuantity) as QuantityOnLocation,
    orders.Quantity as OrderedQuantity,
    sum(batch.BQuantity) as BookedQuantity
from
    #ItemTable item
        left join 
        (
            select Item, Batch, quantity as BQuantity, null as Location, null  as LQuantity from #BookingTable
            union
            select item, Batch, null, Location, Quantity as LQuantity from #LocationTable
        ) batch
            on item.Item = batch.Item
    left join #OrderTable orders
        on item.Item = orders.Item and batch.Batch = orders.Batch
group by 
    item.Item,
    batch.Batch,    
    orders.Quantity