Find all the path between two nodes of Azure SQL database

Question

Hi,

I have defined a graph database in Azure SQL database with pocProcess as Node table which has id as attribute and pocProcessDependency as Edge table. I want to get all the path between the two nodes 1 and 15.

There are two path between 1 and 15:
a. 1 -> 3 -> 10 -> 14 -> 15
b. 1 -> 2 -> 4 -> 10 -> 14 -> 15

SELECT NODE, SUBGRAPH
FROM (
SELECT
i1.id AS NODE,
STRING_AGG(i2.id, '->') WITHIN GROUP (GRAPH PATH) AS SUBGRAPH,
LAST_VALUE(i2.id) WITHIN GROUP (GRAPH PATH) AS LASTNODE
FROM
pocProcess AS i1,
pocProcessDependency FOR PATH AS d,
pocProcess FOR PATH AS i2
WHERE MATCH(SHORTEST_PATH(i1(-(d)->i2)+))
AND i1.id = 1
) AS Q
WHERE Q.LASTNODE = 15

The query returns:

NODE SUBGRAPH
1 3->10->14->15

But I want to get all the paths between 1 and 15. Is there any other way around for this?

Thanks

Answer 1

Hi anonymous user-8682, thanks for passing on the schema and data for tables.

If we go through the article, getting all paths is currently not supported:

However, we can still write the query with workaround as mentioned below. Of course you can tweak the query a little as per requirement.

DECLARE @Result as table  
(   id int, edges varchar(100), parent_id int  
)  
insert into @Result values (1,'1', null)  
insert into @Result  
SELECT  subgraph, subgraph as name, PersonName  
FROM (  
 SELECT  
 i1.ID AS PersonName, STRING_AGG(i2.ID, '->') WITHIN GROUP (GRAPH PATH) AS subgraph,  
 LAST_VALUE(i2.ID) WITHIN GROUP (GRAPH PATH) AS LastNode,COUNT(i2.ID) WITHIN GROUP (GRAPH PATH) AS levels  
 FROM pocProcess AS i1,  
 pocProcessDependency FOR PATH AS d,  
 pocProcess FOR PATH  AS i2  
 WHERE MATCH(SHORTEST_PATH(i1(-(d)->i2)+))  
) AS Q WHERE Q.Levels = 1  
  
;WITH ALLPATHS AS  
(  
    SELECT id, edges as path, parent_id  
    FROM @Result   
    WHERE parent_id IS NULL  
    UNION ALL  
    SELECT t.id, cast(ALLPATHS.path +'>'+ t.edges as varchar(100)), t.parent_id  
    FROM @Result t  
    INNER JOIN ALLPATHS ON t.parent_id = ALLPATHS.id  
)  
  
SELECT path FROM ALLPATHS WHERE id = 15  

Reference: https://rextester.com/WVP72380
Please let me know if this helps or we can discuss further.

----------

if answer helps, please select 'Accept answer' as this could help other community members looking for similar queries.