## Output based on Mapping Table and Orders Table

 Author Message vivekkam Grasshopper Group: General Forum Members Points: 10 Visits: 6 I have two tables,1st Table is the Order Table which has order no,old items, price and their quantity.2nd Table is Mapping Table which has mapping for each unique combinations of the old order items.3rd Table is the desired output.Some notes:1)Whenever in an order there is a combination of item1,item2,item3 of the mapping table it should retrieve output1 and output 2 for that, when in order there is a combination of item1 and item 2 of the mapping table it should retrieve output1 and output 2 for that, when in order there is only item 1 present it should retrieve output1 and output 2 for that.2)Also, price of item2 and item3 will always be 0. The output table should take the price and quantity of Item1 always.3) Its not necessary that Order No will be sequential. It can be like 456,789,989 etc.DDL and DML for my question:declare @orders table ( OrderNo int, OrderItem varchar(10), Quantity int, Price money )declare @mapping table ( Item1 varchar(10), Item2 varchar(10), Item3 varchar(10), Output1 varchar(10), Output2 varchar(10) )insert into @ordersvalues (1, 'A', 3, 960), (1, 'B', 1, 0), (1, 'C', 1, 0), (1, 'D', 2, 200), (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 @mappingvalues ('A', 'B', 'C', 'X', 'S'), ('A', 'B', '', 'P', 'R'), ('A', '', '', 'O', ''), ('D', '', '', 'Z', ''), ('E', 'B', 'C', 'Y', ''), ('Q', '', '', 'M', ''), ('J', 'B', 'C', 'N', '')-- Output Expecteddeclare @output table ( OrderNo int, NewItem varchar(10),Quantity int,Price money)insert into @outputvalues (1, 'X', 3, 960), (1, 'S', 3, 960), (1, 'Z', 2, 200), (2, 'Y', 5, 100), (3, 'M', 6, 1000), (4, 'P', 3, 300), (4, 'R', 3, 300), (5, 'O', 7, 4000)select * from @ordersselect * from @mappingselect * from @output-- Solution that I tried giving wrong outputDECLARE @OutputTable TABLE (orderNo int, newItem varchar(1), quantity int, price money);INSERT INTO @OutputTable(orderNo, newItem, quantity, price) SELECT o.orderNo, m.output1, o.quantity, o.price FROM @mapping as m INNER JOIN @orders as o ON m.item1 = O.orderItem AND o.price != 0 AND m.output1!=''INSERT INTO @OutputTable(orderNo, newItem, quantity, price) SELECT o.orderNo, m.output2, o.quantity, o.price FROM @mapping as m INNER JOIN @orders as o ON m.item1 = O.orderItem AND o.price != 0 AND m.output2!=''SELECT * FROM @OutputTable ORDER BY orderNoI have also attached the solution I tried from my end.Thanks in advance.[url=http://s10.This image host is not supported, please use another/93j6lzb3d/Query_Image.png][/url] Mark Fitzgerald-331224 SSCarpal Tunnel Group: General Forum Members Points: 4803 Visits: 1652 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-- Subqueryselect 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 vivekkam Grasshopper Group: General Forum Members Points: 10 Visits: 6 Hi Mark,What an excellent reply. Thanks a ton. It just lacks one thing and that's because I dint explained that case in my requirement but you asked that question in your reply. Please let me know how to solve that.Regarding your question: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.Ans: In this case in the mapping table will have a mapping for D,B,C ->T and the output should have the result with both the combinations. One for the output for ABC and one for the output BCD. An order can contain products alone , product with B or product with B and C and mapping table would have mapping for all the scenarios. In my expected output I showed a line as 1 | Z | 2 | 200 but this was a mistake from my end. Since order 1 had also B and C in it, the output should retrieve the corresponding output for the combination of (DBC) i.e. T and not for D alone.I have added the changes in Mapping_IN and Mapping_OUT table with the OUTPUT for DBC but its not giving output T in the result. 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))insert into @Order values (1,'A',3,960), (1,'B',1,0), (1,'C',1,0), (1,'D',2,200), (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), ('S8','D',1), ('S8','B',0), ('S8','C',0); insert into @Mapping_OUT values ('S1','X'), ('S1','S'), ('S2','P'), ('S2','R'), ('S3','O'), ('S4','Z'), ('S5','Y'), ('S6','M'), ('S7','N'), ('S8','T');--SELECT * FROM @Mapping_OUT--SELECT * FROM @Mapping_IN--SELECT * FROM @Order--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 matchedselect 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 matchedPlease HELP !! Again thanks a lot for taking time out of your schedule to reply for my post !! vivekkam Grasshopper Group: General Forum Members Points: 10 Visits: 6 Hi Mark,I figured it out myself.Instead of using row_number() if I use rank() or dense_rank() it would take all the combinations keeping the value of RN same for the highest number of match.Again thanks a lot !!I was stuck on it for 4 days and you came up as a life savior. Regards,Vivek