Given the requirement I think that the design of the mapping table needs changing to allow the query to be written in a way to produce the correct results and be flexible for the future. I would propose dividing the mapping table into 2 tables (IN and OUT). Your examples show cases where upto 3 orderitems can be passed in and upto 2 passed out.

Using the table structure below would give the flexibility required.

declare @Order table (OrderID int, OrderItem varchar(10), Quantity int, Price money)

declare @Mapping_IN table (MapID varchar(3), OrderItem varchar(10), MajorProduct int)

declare @Mapping_OUT table (MapID varchar(3), OrderItem varchar(10))

Your example data would then be :

insert into @Order values

(1,'A',3,960),(1,'B',1,0),(1,'C',1,0),(1,'D',1,0),

(2,'E',5,100),(2,'B',1,0),(2,'C',1,0),

(3,'Q',6,1000),

(4,'B',1,0),(4,'A',3,300),

(5,'A',7,4000)

insert into @Mapping_IN values

('S1','A',1),('S1','B',0),('S1','C',0),('S2','A',1),('S2','B',0),('S3','A',1),('S4','D',1),

('S5','E',1),('S5','B',0),('S5','C',0),('S6','Q',1),('S7','J',1),('S7','B',0),('S7','C',0)

insert into @Mapping_OUT values

('S1','X'),('S1','S'),('S2','P'),('S2','R'),('S3','O'),('S4','Z'),('S5','Y'),('S6','N'),('S7','N');

Your requirements state that all orderitems must be present. As OrderID (containing ABC) could match 3 of the mapping rows I have assumed that the one with the most products matching would be required. If you had the case where two mappings, mapping 1 (ABC) and mapping 2 (BCD), which would you require for an order with products ABCD as both would match.

Query to give the result set you outline (first using CTE and second using subqueries).

--CTE

;with MapItemList as

(

select MapID, count(*) as NumberOfItemsInList

from @Mapping_IN

group by MapID

),

MatchesList as

(

select MI.MapID, O.OrderID, count(*) as NumberOfMatches

from @Order as O

inner join @Mapping_IN as MI

on O.OrderItem = MI.OrderItem

group by MI.MapID, O.OrderID

),

FindMatches as

(

select ML.*, row_number() over (partition by ML.OrderID order by ML.NumberOfMatches desc) as RN

from MatchesList as ML

inner join MapItemList as MA

on ML.MapID = MA.MapID and ML.NumberOfMatches = MA.NumberOfItemsInList

)

select O.OrderID, MO.OrderItem, O.Quantity, O.Price

from FindMatches as FM

inner join @Mapping_IN as MI

on FM.MapID = MI.MapID and MI.MajorProduct = 1

inner join @Order as O

on O.OrderID = FM.OrderID and MI.OrderItem = O.OrderItem

inner join @Mapping_OUT as MO

on FM.MapID = MO.MapID

where RN = 1; -- Only allows the best match available i.e. most amount of products matched

-- Subquery

select O.OrderID, MO.OrderItem, O.Quantity, O.Price

from (select ML.*, row_number() over (partition by ML.OrderID order by ML.NumberOfMatches desc) as RN

from (select MI.MapID, O.OrderID, count(*) as NumberOfMatches

from @Order as O

inner join @Mapping_IN as MI

on O.OrderItem = MI.OrderItem

group by MI.MapID, O.OrderID) as ML

inner join (select MapID, count(*) as NumberOfItemsInList

from @Mapping_IN

group by MapID) as MA

on ML.MapID = MA.MapID and ML.NumberOfMatches = MA.NumberOfItemsInList) as FM

inner join @Mapping_IN as MI

on FM.MapID = MI.MapID and MI.MajorProduct = 1

inner join @Order as O

on O.OrderID = FM.OrderID and MI.OrderItem = O.OrderItem

inner join @Mapping_OUT as MO

on FM.MapID = MO.MapID

where RN = 1; -- Only allows the best match available i.e. most amount of products matched

Fitz