In many cases, it is necessary to explicitly check for such loops and exclude the results. For example, Alice is a friend of John John is a friend of Mary and Mary in turn is a friend of Alice. Find friends-of-friends-of-friends, excluding those cases where the relationship "loops back". WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City) Find people who like a restaurant in the same city they live inįROM Person, likes, Restaurant, livesIn, City, locatedIn WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant) Find Restaurants that John's friends likeįROM Person person1, Person person2, likes, friendOf, Restaurant , ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4)) , ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2)) , ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1)) , ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3)) VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2)) , ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3)) , ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2)) VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1)) * Insert data where the restaurants are located */ , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1)) , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
, ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3)) , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2)) VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1)) * Associate in which city live each person*/ , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
, ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9) , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9) , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9) VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9) * Insert which restaurants each person likes */ you need to provide the $node_id from $from_id and $to_id columns. Inserting into a node table is same as inserting into a regular table IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'graphdemo')ĬREATE TABLE likes (rating INTEGER) AS EDGE Sample Script - Create a graph demo database These nodes are connected to each other using Friends, Likes, LivesIn and LocatedIn edges.įigure 1: Sample schema with restaurant, city, person nodes and LivesIn, LocatedIn, Likes edges.
This sample creates a graph schema, as showed in Figure 1, for a hypothetical social network that has People, Restaurant and City nodes. This sample script will work on both Azure SQL Database and SQL Server 2017 (14.x) Sample Schema This sample provides a Transact-SQL script to create a graph database with nodes and edges and then use the new MATCH clause to match some patterns and traverse through the graph. Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance