Graph Extensions: SHORTEST_PATH query with a collection of nodes.

Question

I am using SQL Server graph extentions (node/edge table) to represent a social network. I know how to create a query that will map one person to the shortest path to several people, i.e. Find the shortest route between Alice and the list { Bob, Charles }:

Alice -- somebody -- Bob
Alice -- somebody -- somebody -- Charles

But I don't know how to map one collection of people to another, i.e. shortest path between {Alice,Denise} and {Bob, Charles}

Alice -- somebody -- Bob
Alice -- somebody -- somebody -- Charles
Denise -- Charles
Denise -- somebody -- somebody -- somebody -- somebody -- Bob

Obviously I can just do a union, but is there a way that allows me to do so with one single query?

Answer 1

I think that if you replace '=' with IN in Example A (https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-shortest-path?view=sql-server-ver15#examples), then it should work with multiple sources and targets, i.e. Person1.name IN ('Alice', 'Denice'), Q.LastNode IN ('Bob', 'Charles').

To show the shortest path of found paths, try something like this:

SELECT top(1) with ties PersonName, Friends, levels  
FROM (  
	SELECT  
		Person1.name AS PersonName,  
		STRING_AGG(Person2.name, '->') WITHIN GROUP (GRAPH PATH) AS Friends,  
		LAST_VALUE(Person2.name) WITHIN GROUP (GRAPH PATH) AS LastNode,  
		COUNT(Person2.name) WITHIN GROUP (GRAPH PATH) AS levels  
	FROM  
		Person AS Person1,  
		friend FOR PATH AS fo,  
		Person FOR PATH AS Person2  
	WHERE MATCH(SHORTEST_PATH(Person1(-(fo)->Person2)+))  
	AND Person1.name in ('Alice', 'Denice')  
) AS Q  
WHERE Q.LastNode in ('Bob', 'Charles')  
order by levels  

Answer 2

Hi，@Miroslav Rokva

Welcome to Microsoft T-SQL Q&A Forum!

You may be able to try the above way of writing, but first you need to select @@version to check your database version.
Currently the Graph Extensions (graph processing) you want is only supported in SQL Server 2019 (15.x) and Azure SQL Database, if the above The expert's writing method can't solve your problem. It is recommended that you use cte and traditional join implementation.
Please refer to this link.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.