Share via

Find all the path between two nodes of Azure SQL database

priyanka 0 Reputation points
2024-06-26T21:37:52.4866667+00:00

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,252 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 17,706 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

    0 comments