I have a table with connections between two points:

RidgeId PointId1    PointId2
1   1   2
2   3   2
3   17  10
4   18  10
6   18  11
7   11  3
8   4   1
9   13  4
10  16  13
11  15  16
12  5   15
13  19  5
14  20  19
15  21  20
16  8   21
17  12  8
18  6   12

I would like to find path between two points. f. egz.

from 3 to 10

• 3, 11
• 11, 18
• 18, 10

How to write a query to get this result?

CodePudding user response：

You could use a recursive CTE for this. Since you could have multiple routes, the easiest way to return a resultset is to return it as JSON

• There are two anchors and two recursions because we can go from Point1 to Point2 or v.v. for each node

DECLARE @start int = 3;
DECLARE @end int = 10;

WITH cte AS (
    SELECT
      Point2 = p.PointId2,
      p.RidgeId,
      Json = (
          SELECT p.RidgeId, p.PointId1, p.PointId2
          FOR JSON PATH
      )
    FROM Points p
    WHERE p.PointId1 = @start
    
    UNION ALL
    
    SELECT
      Point2 = p.PointId1,
      p.RidgeId,
      Json = (
          SELECT p.RidgeId, p.PointId1, p.PointId2
          FOR JSON PATH
      )
    FROM Points p
    WHERE p.PointId2 = @start
    
    UNION ALL
    
    SELECT
      Point2 = p.PointId2,
      p.RidgeId,
      Json = JSON_MODIFY(cte.Json, 'append $', JSON_QUERY((
          SELECT p.RidgeId, p.PointId1, p.PointId2
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      )))
    FROM Points p
    JOIN cte ON cte.Point2 = p.PointId1
    WHERE p.RidgeId <> cte.RidgeId

    UNION ALL
    
    SELECT
      Point2 = p.PointId1,
      p.RidgeId,
      Json = JSON_MODIFY(cte.Json, 'append $', JSON_QUERY((
          SELECT p.RidgeId, p.PointId1, p.PointId2
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      )))
    FROM Points p
    JOIN cte ON cte.Point2 = p.PointId2
    WHERE p.RidgeId <> cte.RidgeId
)

SELECT Json
FROM cte
WHERE cte.Point2 = @end;

db<>fiddle

This does not deal well with a loop in the nodes. Ideally you should check for no loops by breaking out the JSON in each recursive section and checking we are not going back on ourselves