How To Find Perfect Match With Multiple Rows Across Tables?

I’m looking for a way to find out which collection of lines from #Orders matches with #Pallets the best.

With the example below with @palletId = 1000 as input to the query the results should only be able to match with ‘Order2’ (100% match) and ‘Order4’ (75% match). In this case my desired result would be ‘Order2’.

Input @palletId = 4000 should have a 100% match to ‘Order4’ and no other matches.

DECLARE @paletId bigint = 1000

CREATE TABLE #Pallets ([PalletId] bigint, [Item] nvarchar(16), [Quantity] int)
CREATE TABLE #Orders ([OrderId] nvarchar(16), [Item] nvarchar(16), [Quantity] int)

INSERT INTO #Pallets ([PalletId], [Item], [Quantity]) VALUES 
(1000, 'item1', 10), 
(1000, 'item2', 10), 
(1000, 'item3', 10),
(4000, 'item1', 10), 
(4000, 'item2', 10), 
(4000, 'item3', 10),
(4000, 'item4', 10)

INSERT INTO #Orders ([OrderId], [Item], [Quantity]) VALUES 
('Order2', 'item1', 10), 
('Order2', 'item2', 10), 
('Order2', 'item3', 10),
('Order1', 'item1', 10),
('Order1', 'item2', 10),
('Order1', 'item3', 5),
('Order3', 'item2', 5),
('Order3', 'item3', 10),
('Order4', 'item1', 10), 
('Order4', 'item2', 10), 
('Order4', 'item3', 10),
('Order4', 'item4', 10),
('Order5', 'item1', 5), 
('Order5', 'item2', 5), 
('Order5', 'item3', 5),

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

Been trying to solve it using the following examples as bases but haven’t managed to get the result that I want.

https://stackoverflow.com/a/27060384/2975371

https://stackoverflow.com/a/104001/2975371

Thanks in advance.

Answer

You can try something like a

declare @cnt int
select @cnt = count(1) 
from #Pallets
where PalletId = @paletId 


select  top 1 OrderId 
from #Orders o
join #Pallets p on 
    o.Item = p.Item and 
    o.Quantity = p.Quantity and 
    p.PalletId = @paletId 
group by OrderId
order by abs(@cnt - count(PalletId))

I just took difference between those two to figure out which one is ‘close’ to 100%. abs(@cnt - count(PalletId)) will only return zero if there is a 100% match