Reputation: 183
With the new SQL Server version there is the function SHORTEST_PATH
.
I use attributes (or Labels) in the edge tables to distinguish the different types of connections.
Unfortunately the SHORTEST_PATH
function doesn't seem to allow any attributes in the where condition (if the tables are marked for path)
SELECT
l1.CommonName AS CommonName,
STRING_AGG(l2.CommonName, '->') WITHIN GROUP (GRAPH PATH) AS Verbindung,
LAST_VALUE(l2.CommonName) WITHIN GROUP (GRAPH PATH) AS LastNode
from object as l1,
connections for path as v,
object for path as l2
where match(SHORTEST_PATH( l1 (-(v)-> l2)+))
and l1.CommonName = 'jagdtWurst'
and v.label= 'hierarchie' <<--- This is not possible .... Error
Is there a no trick to how to do that anyway?
Upvotes: 1
Views: 241
Reputation: 89489
Looks like you can use a subquery in the from
clause. eg
SELECT
l1.CommonName AS CommonName,
STRING_AGG(l2.CommonName, '->') WITHIN GROUP (GRAPH PATH) AS Verbindung,
LAST_VALUE(l2.CommonName) WITHIN GROUP (GRAPH PATH) AS LastNode
from object as l1,
(select * from connections where label = 'hierarchie') for path as v,
object for path as l2
where match(SHORTEST_PATH( l1 (-(v)-> l2)+))
and l1.CommonName = 'jagdtWurst'
Upvotes: 2