Find all the path between two nodes of Azure SQL database

priyanka 0

I wan to get columns from association table in SELECT caluse , Is there any other way around for this?

SELECT subgraph, subgraph as name, Typecode,aid,nbr

FROM (

SELECT

i1.Typecode AS Typecode,

i1.ID AS aid,

i1.Number AS nbr,

STRING_AGG(i2.code, '->') WITHIN GROUP (GRAPH PATH) AS subgraph,

LAST_VALUE(i2.code) WITHIN GROUP (GRAPH PATH) AS LastNode,

COUNT(i2.code) WITHIN GROUP (GRAPH PATH) AS levels

FROM en AS i1,

rela FOR PATH AS d,

en FOR PATH AS i2

WHERE MATCH(SHORTEST_PATH(i1(-(d)->i2)+))

) AS Q WHERE Q.Levels = 1

Thanks

Oury Ba-MSFT 17,556 Reputation points Microsoft Employee

2024-06-27T23:47:38.56+00:00

@priyanka Thank you for your reaching out.

Are you trying to find the shortest path between two nodes?
priyanka 0 Reputation points

2024-06-28T13:50:57.6+00:00

Yes,I'm trying find the shortest path between two nodes
Oury Ba-MSFT 17,556 Reputation points Microsoft Employee

2024-06-28T15:12:12.3233333+00:00

@priyanka

Thank you for getting back.

There's no easy way to find ALL paths between 2 nodes of a given database. It's computationally also a very expensive problem. We don't have a native equivalent in the SQL Graph implementation. Conceptually, you could accomplish something similar by doing dynamic SQL and cross joins, but it's very expensive.

Regards,

Oury