How to append multiple tables to a non-primary key?

Question

I am trying to create a view in which the user sees one row in a “Package”, connected so that when the “Batch” from different tables matches, then they should go together as one row. But if each table itself has a “package”, 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 not understand 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) in your last connection, this bit:

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

If this is not the case:

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

I rewrote your query a bit:

 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; 

What seems more readable to me, but I believe this is a personal preference?

Then I realized that this still does not give you what you want, so I reorganized it again to get this (which gives 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 about the logic for your last column, but this gives the desired results for the other columns.

To get a query based on orders or locations displayed as a package, I combined the two tables in the query.

I would suggest, if possible, revising the structure 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 

Answer 3

I think I would prefer a different approach. How about you take all the batchumbers and add all the columns as empty columns. Then you update the different columns from each table.

Like this:

 SELECT INTO #MyTableResult Batch FROM TableA UNION SELECT Batch FROM TableB 

And so on. Union deleted duplicates.

Then you update as:

 Update #MyTableResult SET Column A = ValueA FROM TableA WHERE #MyTableResult.Batch = TableA.Batch. 

After completing all the updates from your tables, you will get the desired result.