CODEWITHSUNDEEP
sql-server
user3606585
user3606585

Reputation: 23

Get Possible path between two nodes in SQL Server

I need some help to solve a query

I have two tables like this:

Source       Destination
------      -------------
  1              2
  2              3
  3              4
  3              5

When user will input source = 1 and destinaton = 5 then it should populate the following things

1        2
2        3
3        5

Upvotes: 2

Views: 557

Answers (2)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

This may help you

WITH CTE_SD
AS
(   SELECT  [Source],[Destination] FROM SD WHERE [Destination] = 5
    UNION ALL
    SELECT  SD.[Source],SD.[Destination]
    FROM    SD
    INNER JOIN CTE_SD   ON  SD.[Destination]    =   CTE_SD.[Source]

)
SELECT * FROM CTE_SD ORDER BY [Source]

... WHERE [Destination] = 5 - Here you can use you Parameter or variable. Also you can use your additional logic inside the CTE

Upvotes: 1

patito
patito

Reputation: 540

Sounds a lot like a seven degrees separation problem, taken from another post answered in Stack Overflow, I would recommend you to look at the following solution:

Degrees of Separation Query

Good luck!

Upvotes: 0

Related Questions