identifying many to many relationship between colu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

identifying many to many relationship between colu

Author

Topic

LonnieM
Starting Member

15 Posts

Posted - 2013-11-11 : 15:32:17

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-11 : 15:57:55

What is the logic you need the apply here?

LonnieM
Starting Member

15 Posts

Posted - 2013-11-11 : 16:09:46

I am trying to add the "ID" column and identify with a single identifier, all items related to each other. The numbers in the bottom example illustrate what I'm trying to accomplish.

Thanks,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-11 : 17:29:02

Here is "a" solution. It is probably very fragile solution (I don't have time to come up with a robust solution),but it works with your sample data:

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

LonnieM
Starting Member

15 Posts

Posted - 2013-11-12 : 08:59:02

Thank you Lamprey....I will read through the code and hopefully give it a try this afternoon.

LonnieM
Starting Member

15 Posts

Posted - 2013-11-12 : 10:48:17

BINGO!! Thank you very much Lamprey....I am in the process of validating but with a minor change in data types (to accomodate leading "0"s) this appears to have worked.
Thank you much.
Lonnie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources