Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

identifying many to many relationship between columns Expand / Collapse
Author
Message
Posted Monday, November 11, 2013 1:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
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 0224181
0224180 0224190
0224181 0224180
0224181 0224190
0224190 0224180
0224190 0224181
0202294 0202295
0202295 0202294
0209250 0209251
0209251 0209250

I 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 ID
0224180 0224181 1
0224180 0224190 1
0224181 0224180 1
0224181 0224190 1
0224190 0224180 1
0224190 0224181 1
0202294 0202295 2
0202295 0202294 2
0209250 0209251 3
0209251 0209250 3

I hope this is clear and can attach a script or data if necessary.

Thanks in Advance,
Lonnie
Post #1513240
Posted Monday, November 11, 2013 4:01 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 579, Visits: 3,782
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)--Product

where the LineItem table breaks the M-M relationship between Invoice and Product into two 1-M relationships.
Post #1513287
Posted Monday, November 11, 2013 8:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 3,596, Visits: 5,112
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 @T1
SELECT 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
UNION ALL SELECT 1, 2
UNION ALL SELECT 1, 3
UNION ALL SELECT 1, 4
UNION ALL SELECT 2, 3
UNION ALL SELECT 2, 4
UNION ALL SELECT 3, 4
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 1
UNION ALL SELECT 3, 2
UNION ALL SELECT 4, 2
UNION ALL SELECT 4, 3
UNION ALL SELECT 7, 8
UNION ALL SELECT 7, 9
UNION ALL SELECT 8, 9
UNION ALL SELECT 8, 7
UNION ALL SELECT 9, 7
UNION 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=rn
FROM rCTE
WHERE n = 2
ORDER 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!
Post #1513315
Posted Tuesday, November 12, 2013 6:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
Thank you Dwain. I'll read through the code and give it a try this morning!
Post #1513444
Posted Tuesday, November 12, 2013 7:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
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 1
Types 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,
Post #1513461
Posted Tuesday, November 12, 2013 8:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
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 Loop
SELECT TOP 1
@Val1 = Item1,
@Val2 = Item2
FROM
@Foo
ORDER BY
Item1

WHILE @Val1 IS NOT NULL
BEGIN

-- 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;
END

SELECT *
FROM @Foo
Post #1513499
Posted Tuesday, November 12, 2013 5:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 3,596, Visits: 5,112
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 1
Types 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 @T1
SELECT '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=rn
FROM rCTE
WHERE n = 2
ORDER 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!
Post #1513663
Posted Tuesday, November 12, 2013 5:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 3,596, Visits: 5,112
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!
Post #1513666
Posted Wednesday, November 13, 2013 7:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
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 18
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'WHERE'.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'WHERE'.

I will try to get back to this this afternoon but thought I owed you a response.
Post #1513906
Posted Wednesday, November 13, 2013 9:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 15, 2013 12:59 PM
Points: 474, Visits: 187
Correction....those errors were due to my sloppy typing....but back to the original error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "STD SKU" of recursive query "rCTE".
Post #1513945
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse