CODEWITHSUNDEEP
sql-serversql-server-2019sql-graphsql-server-graphgraph-table
Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

SQL Server Graph Tables - navigate through certain nodes to find end

I have a structure of graph tables containing 2 Node tables and 1 Edge table.

Symptom table is a Node table and Syndrome_Causes is an Edge table. Also, there is another Node table called Syndrome.

My goal is to have a query where I can traverse my graph, going through specific nodes.

I have written the below query to show you all possible routes, starting from the "Cardiovascular" node, but I would need to filter out the results going through the path, underlined with red in the following image (Cardiovascular->Palpitations->Duration->Interval->Days).

select distinct
    s1.symptom_name
    , string_agg(s2.symptom_name, '->') within group (graph path) AS links
    , last_value(s2.symptom_name) within group (graph path) as last_match
from symptom as s1,
    syndrome_causes for path as sc1,
    symptom         for path as s2
where match(
    shortest_path(s1(-(sc1)->s2)+)
    )
    and s1.symptom_name = 'Cardiovascular'

enter image description here

I have thought about adding another filtering condition to the WHERE clause, but I can't figure out how I could write it so that I constrain the node to go only through the values in the "links" column.

Mind you, I know and I can pass it the value in the links column to filter the rows and get me to the "Days" value in last_match column, but my intention is to filter it by following a certain path through nodes (a verification), something like below:

where match(
    shortest_path(s1(-(sc1)->s2)+)
    )
    and s1.symptom_name = 'Cardiovascular'
    and s2.symptom_name = ALL('Palpitations, 'Duration', 'Interval', 'Days')

However, this gives out an error:

The alias or identifier 's2.symptom_name' cannot be used in the select list, order by, group by, or having context.

So, is there a way to constrain a path search to go through certain nodes?

Upvotes: 0

Views: 387

Answers (1)

lptr
lptr

Reputation: 6808

So, is there a way to constrain a path search to go through certain nodes?

It is possible to filter/limit the nodes to be considered for the path, by selecting only specific values "for path", but this does not dictate how the path is traversed.

......
from symptom as s1,
syndrome_causes for path as sc1,
(select * from symptom where symptom_name in ('Palpitations', 'Duration', 'Interval', 'Days')) for path as s2
......

Upvotes: 1

Related Questions