Get Possible path between two nodes in SQL Server

Question

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

Unfortunately, I do not understand remotely what you are trying to accomplish or what you're expecting (nor the behavior you're expecting from SQL in between). Do you expect one resulting table or two? What is the reason to show all but the third row from both tables? Please elaborate. — Ralph, Mar 16 '16 at 13:22

Abdul Rasheed · Accepted Answer · 2016-03-16T13:02:22.760

1

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

edited Mar 16 '16 at 13:02

answered Mar 16 '16 at 12:53

Abdul Rasheed

6,486
4
32
48

1

I cannot figure out how to modify this code to use [Source] and [Destination] as parameters. How can I do it ? – ihebiheb Jun 08 '16 at 11:04

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

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!

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 16 '16 at 12:55

patito

530
2
13

Get Possible path between two nodes in SQL Server

2 Answers2