0

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
Improve this question
3
  • 1) Do you need to retrieve the whole link tree by any its member? ) How do you prevent link loops in relational table? Anycase recursive CTE needed...
    – Akina
    Commented Mar 19, 2020 at 13:56
  • 1) yes, the idea is to pass in any code in the chain and retrieve the whole chain 2) This will handled in code although I'm open to other options if there is a better solution
    – DiscoDamo
    Commented Mar 19, 2020 at 14:00
  • I will look into recursive CTE. thanks
    – DiscoDamo
    Commented Mar 19, 2020 at 14:01

1 Answer 1

Reset to default
0

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.