Sql-server – Many to Many Link Table Return full chain from one record

sql server

I am looking to run a query on a many to many link table which chains records together and displays all the linked records. I've included a simplified data set for copy/paste as an example.

I have a table of objects that include a code and a description which are linked to each other using a link table. I want to pass in a code from one object and return all the objects in the chain that includes it. I can pull back results directly linked to the input, but I am struggling to extend this to include all links in the chain. Any help would be much appreciated.

Thanks, D.

-- Create Tables
DECLARE @TestObject TABLE(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Code NVARCHAR(8) NOT NULL,
Description NVARCHAR(30) NOT NULL
)

DECLARE @TestRelationships TABLE(
ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Link1 INT NOT NULL,
Link2 INT NOT NULL
)

-- Populate Object Data
INSERT INTO @TestObject VALUES ('TEST1111', 'Test Object 1')
INSERT INTO @TestObject VALUES ('TEST2222', 'Test Object 2')
INSERT INTO @TestObject VALUES ('TEST3333', 'Test Object 3')
INSERT INTO @TestObject VALUES ('TEST4444', 'Test Object 4')
INSERT INTO @TestObject VALUES ('TEST5555', 'Test Object 5')
INSERT INTO @TestObject VALUES ('TEST6666', 'Test Object 6')
INSERT INTO @TestObject VALUES ('TEST7777', 'Test Object 7')
INSERT INTO @TestObject VALUES ('TEST8888', 'Test Object 8')
INSERT INTO @TestObject VALUES ('TEST9999', 'Test Object 9')

-- Populate Link Chain 1
INSERT INTO @TestRelationships VALUES (1, 2) -- Links 1 AND 2
INSERT INTO @TestRelationships VALUES (1, 3) -- Links 1 AND 3
INSERT INTO @TestRelationships VALUES (1, 4) -- Links 1 AND 4
INSERT INTO @TestRelationships VALUES (3, 5) -- Links 3 AND 5
INSERT INTO @TestRelationships VALUES (5, 6) -- Links 5 AND 6

-- Populate Link Chain 2
INSERT INTO @TestRelationships VALUES (7, 8) -- Links 7 AND 8
INSERT INTO @TestRelationships VALUES (8, 9) -- Links 8 AND 9

-- Select Records (inc. some results)
-- TEST1111: Shows 1, 2, 3, 4 (Required to show 1, 2, 3, 4, 5, 6)
-- TEST9999: Shows 8, 9 (Required to show 7, 8, 9)

DECLARE @input NVARCHAR(8)
SET @input = 'TEST1111'

SELECT DISTINCT O1.* FROM @TestObject O1
JOIN @TestRelationships TR
ON O1.ID = TR.Link1
OR O1.ID = TR.Link2
JOIN @TestObject O2
ON (O2.ID = TR.Link1 AND O2.ID <> O1.ID)
OR (O2.ID = TR.Link2 AND O2.ID <> O1.ID)
WHERE O1.Code = @input OR o2.Code = @input

Best Answer

Just to follow up with a solution, with thanks to Akina in the comments.

I had to convert the above example to a proper table, and use the following select:

WITH Test (ID, Link1, Link2) AS
(SELECT O.ID, R.Link1, R.Link2 
FROM TestObject AS O
LEFT OUTER JOIN TestRelationships AS R
ON R.Link1 = O.ID
WHERE O.ID NOT IN (SELECT Link2 FROM TestRelationships)
 AND O.Code = 'TEST1111'
UNION ALL
SELECT T.ID, R1.Link1, R1.Link2
FROM TestRelationships AS R1
INNER JOIN Test AS T
ON T.Link2 = R1.Link1)
SELECT * FROM Test