如何基于非主键正确关联多表？批量数据视图构建异常排查

Got it, let's sort out this query for you. The main problem with your current SQL is that it starts from #ItemTable and chains left joins, which misses batches that only exist in #OrderTable or #BookingTable (like the 55555 batch for item 1000). Your join conditions for the order and booking tables are also overly complex, causing unintended filtering that prevents matching batches from being combined correctly.

The Solution Approach #

Instead of starting from the item list, we first collect all unique (Item, Batch) combinations from the three data tables. This ensures every batch—regardless of which table it comes from—gets its own row. Then we left join each data table to this base set to pull in the corresponding values for each column.

Corrected SQL Query #

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)

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)

WITH AllItemBatches AS (
    -- Collect every unique Item + Batch pair from all data tables
    SELECT Item, Batch FROM #LocationTable
    UNION
    SELECT Item, Batch FROM #OrderTable
    UNION
    SELECT Item, Batch FROM #BookingTable
)
SELECT
    a.Item,
    a.Batch,
    l.Location,
    l.Quantity AS QuantityOnLocation,
    o.Quantity AS OrderedQuantity,
    -- Use ISNULL if you want to show 0 instead of NULL for missing bookings
    ISNULL(b.Quantity, 0) AS BookedQuantity
FROM AllItemBatches a
LEFT JOIN #LocationTable l 
    ON a.Item = l.Item AND a.Batch = l.Batch
LEFT JOIN #OrderTable o 
    ON a.Item = o.Item AND a.Batch = o.Batch
LEFT JOIN #BookingTable b 
    ON a.Item = b.Item AND a.Batch = b.Batch
-- Filter out rows where all quantities are 0 or NULL
WHERE 
    ISNULL(l.Quantity, 0) <> 0 
    OR ISNULL(o.Quantity, 0) <> 0 
    OR ISNULL(b.Quantity, 0) <> 0
ORDER BY a.Item, a.Batch;

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

What This Does #

1. AllItemBatches CTE: Gathers every unique (Item, Batch) combination from #LocationTable, #OrderTable, and #BookingTable using UNION (which automatically removes duplicates).
2. Left Joins: Each data table is joined to this base set on both Item and Batch, ensuring we only pull values that match the exact batch for each item.
3. Filtering: The WHERE clause keeps only rows where at least one quantity is non-zero (matching your original requirement).
4. Optional NULL Handling: The ISNULL(b.Quantity, 0) converts missing booking quantities to 0 (as seen in your expected result for batch 22). Remove the ISNULL if you prefer NULL instead.

Expected Result #

Running this query will produce exactly the output you're looking for:

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