## identifying many to many relationship between columns

 Author Message lmeinke SSC Eights! Group: General Forum Members Points: 999 Visits: 199 Hello All,I am struggling with something that is conceptually simple but I'm at a loss at how to implement it. I have two columns with a many to many relationship between the columns;ITEM1 ITEM2 0224180 02241810224180 02241900224181 02241800224181 02241900224190 02241800224190 02241810202294 02022950202295 02022940209250 02092510209251 0209250I need to add a single identifier that will allow these cross referenced parts to be queried. The desired outcome would be something like;ITEM1 ITEM2 ID0224180 0224181 10224180 0224190 10224181 0224180 10224181 0224190 10224190 0224180 10224190 0224181 10202294 0202295 20202295 0202294 20209250 0209251 30209251 0209250 3I hope this is clear and can attach a script or data if necessary.Thanks in Advance,Lonnie pietlinden SSC-Dedicated Group: General Forum Members Points: 31596 Visits: 15129 Not sure you can show a many-to-many relationship without a child table. That's just the standard way to do it... the standard example is Invoice--(1,M)--LineItem---(M,1)--Productwhere the LineItem table breaks the M-M relationship between Invoice and Product into two 1-M relationships. dwain.c SSC-Forever Group: General Forum Members Points: 43693 Visits: 6431 PL is probably correct in the way this should be handled from a theoretical perspective.On the other hand, this may work but also may not be particularly performant.`DECLARE @T1 TABLE (ITEM1 INT, ITEM2 INT);INSERT INTO @T1SELECT 0224180,0224181UNION ALL SELECT 0224180,0224190UNION ALL SELECT 0224181,0224180UNION ALL SELECT 0224181,0224190UNION ALL SELECT 0224190,0224180UNION ALL SELECT 0224190,0224181UNION ALL SELECT 0202294,0202295UNION ALL SELECT 0202295,0202294UNION ALL SELECT 0209250,0209251UNION ALL SELECT 0209251,0209250UNION ALL SELECT 1, 2UNION ALL SELECT 1, 3UNION ALL SELECT 1, 4UNION ALL SELECT 2, 3UNION ALL SELECT 2, 4UNION ALL SELECT 3, 4UNION ALL SELECT 2, 1UNION ALL SELECT 3, 1UNION ALL SELECT 4, 1UNION ALL SELECT 3, 2UNION ALL SELECT 4, 2UNION ALL SELECT 4, 3UNION ALL SELECT 7, 8UNION ALL SELECT 7, 9UNION ALL SELECT 8, 9UNION ALL SELECT 8, 7UNION ALL SELECT 9, 7UNION ALL SELECT 9, 8;WITH rCTE AS ( SELECT n=0, ITEM ,rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,ITEM1=ITEM ,ITEM2=NULL FROM ( SELECT DISTINCT ITEM=MIN(b.ITEM1) FROM ( SELECT ITEM1, ITEM2, rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM @T1 ) a CROSS APPLY ( SELECT ITEM1=CASE WHEN ITEM1 < ITEM2 THEN ITEM1 ELSE ITEM2 END ,ITEM2=CASE WHEN ITEM1 < ITEM2 THEN ITEM2 ELSE ITEM1 END FROM @T1 b WHERE a.ITEM1 IN (b.ITEM1, b.ITEM2) ) b GROUP BY rn ) a UNION ALL SELECT n+1, b.ITEM1, rn, b.ITEM1, b.ITEM2 FROM rCTE a JOIN @T1 b ON a.ITEM = b.ITEM1 WHERE n <= 1 UNION ALL SELECT n+1, b.ITEM1, rn, b.ITEM1, b.ITEM2 FROM rCTE a JOIN @T1 b ON a.ITEM = b.ITEM2 WHERE n <= 1)SELECT DISTINCT ITEM1, ITEM2, ID=rnFROM rCTEWHERE n = 2ORDER BY rn, ITEM1, ITEM2`Note that I added some additional sample data because I wanted to see how it would resolve out with an additional set of triplet product IDs and also a 4-tuple of product IDs.I'm assuming you don't care which set of grouped products gets which ID. Just that each group is numbered independently.Edit: Slightly simplified the query. Note that this may only work if all permutations appear in the adjacency list. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables lmeinke SSC Eights! Group: General Forum Members Points: 999 Visits: 199 Thank you Dwain. I'll read through the code and give it a try this morning! lmeinke SSC Eights! Group: General Forum Members Points: 999 Visits: 199 On a quick read through I noticed your data type is int. This would drop the leading zeros on many of my skus. When I run this with the varchar type I get:Msg 240, Level 16, State 1, Line 1Types don't match between the anchor and the recursive part in column "STD SKU" of recursive query "rCTE".I'll keep working through this but thought I'd throw that out there if you have a quick reply.Thanks, lmeinke SSC Eights! Group: General Forum Members Points: 999 Visits: 199 I had another suggestion (from a different forum) that provided a solution. It may not be the most eloquent but it ran on 98K rows in 8 minutes.This is from "Lamprey" on the SQLTeam .com forum:DECLARE @Foo TABLE (ITEM1 INT, ITEM2 INT, ID INT)INSERT @Foo (ITEM1, ITEM2) VALUES(0224180, 0224181),(0224180, 0224190),(0224181, 0224180),(0224181, 0224190),(0224190, 0224180),(0224190, 0224181),(0202294, 0202295),(0202295, 0202294),(0209250, 0209251),(0209251, 0209250)DECLARE @Val1 INT;DECLARE @Val2 INT;DECLARE @Iterator INT = 1;-- Prime LoopSELECT TOP 1 @Val1 = Item1, @Val2 = Item2 FROM @FooORDER BY Item1WHILE @Val1 IS NOT NULLBEGIN -- Perform Update UPDATE @Foo SET ID = @Iterator WHERE Item1 IN (@Val1, @Val2) OR Item2 IN (@Val1, @Val2); -- Get next item/group SELECT TOP 1 @Val1 = Item1, @Val2 = Item2 FROM @Foo WHERE Item1 NOT IN (@Val1, @Val2) AND Item2 NOT IN (@Val1, @Val2) AND ID IS NULL ORDER BY Item1 IF @@ROWCOUNT = 0 BEGIN SET @Val1 = NULL; END SET @Iterator = @Iterator + 1;ENDSELECT *FROM @Foo dwain.c SSC-Forever Group: General Forum Members Points: 43693 Visits: 6431 lmeinke (11/12/2013)On a quick read through I noticed your data type is int. This would drop the leading zeros on many of my skus. When I run this with the varchar type I get:Msg 240, Level 16, State 1, Line 1Types don't match between the anchor and the recursive part in column "STD SKU" of recursive query "rCTE".I'll keep working through this but thought I'd throw that out there if you have a quick reply.Thanks,That error is pretty simple to resolve by CASTing the NULL assignment to ITEM2 in the rCTE anchor to a VARCHAR type, the same as your item codes.`DECLARE @T1 TABLE (ITEM1 VARCHAR(20), ITEM2 VARCHAR(20));INSERT INTO @T1SELECT '0224180','0224181'UNION ALL SELECT '0224180','0224190'UNION ALL SELECT '0224181','0224180'UNION ALL SELECT '0224181','0224190'UNION ALL SELECT '0224190','0224180'UNION ALL SELECT '0224190','0224181'UNION ALL SELECT '0202294','0202295'UNION ALL SELECT '0202295','0202294'UNION ALL SELECT '0209250','0209251'UNION ALL SELECT '0209251','0209250';WITH rCTE AS ( SELECT n=0, ITEM ,rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,ITEM1=ITEM ,ITEM2=CAST(NULL AS VARCHAR(20)) FROM ( SELECT DISTINCT ITEM=MIN(b.ITEM1) FROM ( SELECT ITEM1, ITEM2, rn=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM @T1 ) a CROSS APPLY ( SELECT ITEM1=CASE WHEN ITEM1 < ITEM2 THEN ITEM1 ELSE ITEM2 END ,ITEM2=CASE WHEN ITEM1 < ITEM2 THEN ITEM2 ELSE ITEM1 END FROM @T1 b WHERE a.ITEM1 IN (b.ITEM1, b.ITEM2) ) b GROUP BY rn ) a UNION ALL SELECT n+1, b.ITEM1, rn, b.ITEM1, b.ITEM2 FROM rCTE a JOIN @T1 b ON a.ITEM = b.ITEM1 WHERE n <= 1 UNION ALL SELECT n+1, b.ITEM1, rn, b.ITEM1, b.ITEM2 FROM rCTE a JOIN @T1 b ON a.ITEM = b.ITEM2 WHERE n <= 1)SELECT DISTINCT ITEM1, ITEM2, ID=rnFROM rCTEWHERE n = 2ORDER BY rn, ITEM1, ITEM2;`I would love to hear how this performs against your 98K rows.Edit: Minor edit to the sample data. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSC-Forever Group: General Forum Members Points: 43693 Visits: 6431 BTW. I thought this was a really fun problem. Best I've seen on the forums in weeks.I have an idea for another way to solve it that might be just a tad faster than my rCTE, which I'm betting will beat the pants off the RBAR UPDATE that other forum suggested. I'm talking about the rCTE I already posted that is.Setting up a performance test harness might be a bit of a challenge. Since you have piqued my interest, I may continue on it for a bit.Also, about the note where I indicated the product combinations present must include all permutations. I have a way around that now also, in case you need it. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables lmeinke SSC Eights! Group: General Forum Members Points: 999 Visits: 199 Dwain,I appreciate your interest and effort on this. I would like to find a more efficient way to handle this but after changing the ITEM1 and ITEM2 to the appropriate column names and changing @T1 to my actual table name I get these errors;Msg 102, Level 15, State 1, Line 18Incorrect syntax near '='.Msg 156, Level 15, State 1, Line 29Incorrect syntax near the keyword 'WHERE'.Msg 156, Level 15, State 1, Line 34Incorrect syntax near the keyword 'WHERE'.I will try to get back to this this afternoon but thought I owed you a response. lmeinke SSC Eights! Group: General Forum Members Points: 999 Visits: 199 Correction....those errors were due to my sloppy typing....but back to the original error:Msg 240, Level 16, State 1, Line 1Types don't match between the anchor and the recursive part in column "STD SKU" of recursive query "rCTE".