MSSQL Permutations/Combinations - Finding all possible matches using data subset

Question

In MSSQL, I have a table (ProductRecipe) that contains up to 5 different components products. I then have a dataset that contains the individual component products and their costs.

What I'm trying to do is find all the different permutations/combinations that will satisfy my recipe.

CREATE TABLE #ProductRecipe (ProductRecipeID INT, Component1 INT, Component2 INT, Component3 INT, Component4 INT, Component5 INT)
CREATE TABLE #ComponentPricing (RowID INT, PricingID INT, ProductID INT, ProductDescription VARCHAR(50), Cost DECIMAL(18,6))

INSERT INTO #ProductRecipe (ProductRecipeID, Component1, Component2) VALUES (21, 130, 468)
INSERT INTO #ComponentPricing (RowID, PricingID, ProductID, ProductDescription, Cost)
VALUES (1, 314023, 130, 'ULS2', 1.783800)
 , (2, 313616, 130, 'ULS2', 1.783800)
 , (3, 313071, 130, 'ULS2', 1.794000)
 , (4, 312865, 130, 'ULS2', 1.789500)
 , (5, 316323, 468, 'B100', 1.550500)

SELECT * FROM #ProductRecipe
SELECT * FROM #ComponentPricing

DROP TABLE #ProductRecipe
DROP TABLE #ComponentPricing

The result I'm trying to achieve is that I end up with 4 different variations of the recipe since the first 4 records (for ProductID 130) can be blended with the last record (ProductID 468). Only these two products can be blended because those are the two component products defined in my ProductRecipe table.

Desired Result: Row 1+5 go together, Row 2+5 go together, Row 3+5 go together, Row 4+5 go together; Returning the PricingID column.

ProductRecipeID Component1 Component2 Component3 Component4 Component5
21              314023     316323   
21              313616     316323   
21              313071     316323   
21              312865     316323

https://codeblog.jonskeet.uk/2012/11/24/stack-overflow-question-checklist/ — Joe Stefanelli, Dec 21 '18 at 17:28
What's the specific database engine? PostgreSQL, Oracle, DB2, etc. Most likely the solution is quite database-specific. — The Impaler, Dec 21 '18 at 17:57
Why all these redundancies? The recipe ID and the component number of the recipe in the component table but also the component product ID in the recipe and implicitly the component number. Then why are there five columns for components instead of just a table listing the components for each recipe? I suggest you revise that design. — sticky bit, Dec 21 '18 at 18:34
Great point. At the point I was stuck, this is the data that I had. Technically, the reason that there is redundancy is because I've filtered a much larger subset of data to only include the products that exist in my recipe. I will update the sample code to be more clear. Thank you! — Jerad Skinner, Dec 21 '18 at 18:43
I've formatted the question better and have re-asked it. I'm not sure if I should have edited this post or not. The answers here were helpful but not exactly what I needed (due to my poor eamples) [link](https://stackoverflow.com/questions/53893119/mssql-permutations-combinations-without-looping) — Jerad Skinner, Dec 22 '18 at 04:51

score 1 · Accepted Answer · answered Dec 21 '18 at 19:50

See if this works.

DECLARE @ProductRecipe TABLE (ProductRecipeID INT, Component1 INT, Component2 INT, Component3 INT, Component4 INT, Component5 INT) 
DECLARE @ComponentPricing TABLE (RowID INT, PricingID INT, ProductID INT, ProductDescription VARCHAR(50), Cost DECIMAL(18,6)) 

INSERT INTO @ProductRecipe (ProductRecipeID, Component1, Component2,Component3) VALUES (21, 130, 468,221)
INSERT INTO @ComponentPricing (RowID, PricingID, ProductID, ProductDescription, Cost)
VALUES (1, 314023, 130, 'ULS2', 1.783800)
 , (2, 313616, 130, 'ULS2', 1.783800)
 , (3, 313071, 130, 'ULS2', 1.794000)
 , (4, 312865, 130, 'ULS2', 1.789500)
 , (5, 316323, 468, 'B100', 1.550500)
 , (6, 316322, 221, 'B1110', 1.5250500)

;WITH UnpivotedRecipe AS
(
    SELECT 
        ProductRecipeID, ComponentID
    FROM
        (SELECT * FROM @ProductRecipe) AS P
        UNPIVOT(ComponentID FOR V IN(Component1,Component2,Component3,Component4,Component5))AS UP
)
, JoinedData AS
(
    SELECT 
        ProductRecipeID, ComponentID, RowID
    FROM 
        UnpivotedRecipe R
        INNER JOIN @ComponentPricing C ON C.ProductID = R.ComponentID

)
SELECT DISTINCT J1.ComponentID,J1.RowID,J2.ComponentID FROM JoinedData J1
CROSS JOIN JoinedData J2
WHERE
    J1.ComponentID<>J2.ComponentID

This is amazing! Thank you so much. The only thing I'm trying to figure out now is how to stop the duplicate combinations. I didn't specify that requirement in my original post. Basically, I'm getting the combinations twice; just in reverse order. — Jerad Skinner, Dec 21 '18 at 20:14
Also, I believe in the final WHERE clause, we need J1.ProductRecipe = J2.ProductRecipe; without it, this fails when having more than one recipe defined. — Jerad Skinner, Dec 21 '18 at 20:24
Yes, I see that. There needs to be a left join against then final result with a possible derived field for ROW_NUMBER() Partitioned by the left joined ProductReciept inside a case statement to make the NULLS =1 and perhaps a final filter where the derived field=1. I will try later and see if it will work. If not, then this is as close as I could get in time :( — Ross Bush, Dec 21 '18 at 20:30

score 0 · Answer 2 · answered Dec 21 '18 at 20:33

Consider multiple LEFT JOIN on self-joining tables:

SELECT p.ProductRecipeID, c1.PricingID AS Component1, c2.PricingID AS Component2,
       c3.PricingID AS Component3, c4.PricingID AS Component4
FROM #ProductRecipe p
LEFT JOIN #ComponentPricing c1
    ON p.Component1 = c1.ProductID   
LEFT JOIN #ComponentPricing c2
    ON p.Component2 = c2.ProductID
LEFT JOIN #ComponentPricing c3
    ON p.Component3 = c3.ProductID
LEFT JOIN #ComponentPricing c4
    ON p.Component4 = c4.ProductID

Rextester Demo

MSSQL Permutations/Combinations - Finding all possible matches using data subset

2 Answers2