How to select from SQL Server Graph Table When Where Clause Not Match

Question

I'm using Graph Table in SQL Server .

This is my table:

--Node Table
CREATE TABLE [dbo].[Users]
(
    [ID] [int] NOT NULL Primary key,
    [FName] [nvarchar](100) NULL,
    [LName] [nvarchar](100) NULL
)AS NODE
--Edge Table
CREATE TABLE [dbo].[FriendsOf] AS EDGE

How can I select all User1 friend of User2 and User2 is friend of User3 and User1 and User3 don't have direct Edge between them.

I can write this query like this :

 select distinct 
     u1.FName + ' ' + u1.LName as FirstFullName, 
     u2.FName + ' ' + u2.LName as SecondFullName,
     u3.FName + ' ' + u3.LName as ThirdFullName
 from
     Users u1, FriendsOf fo1, Users u2, FriendsOf fo2, Users u3
 where 
     match(u1-(fo1)->u2-(fo2)->u3) 
     and not exists(select 1 from friendsof fof 
                    where (fof.$from_id = u1.$node_id and fof.$to_id = u3.$node_id) or (fof.$from_id = u3.$node_id and fof.$to_id = u1.$node_id)
 )

but I want to understand other way?

i want to use code like this :

select distinct u1.FName + ' ' + u1.LName as FirstFullName, u2.FName + ' ' + u2.LName as SecondFullName,u3.FName + ' ' + u3.LName as ThirdFullName
from Users u1 , FriendsOf fo1 , Users u2 , FriendsOf fo2 , Users u3 , FriendsOf fo3,, FriendsOf fo4
where match(u1-(fo1)->u2-(fo2)->u3) and (not match(u1-(fo3)->u3) or not match(u3(fo4)->u1))

Please help me solve this problem.

@marc-s you Edit my Question - so Answer my question – mhsankar Jun 26 '18 at 07:01 — mhsankar, Jun 26 '18 at 07:01

score 5 · Answer 1 · answered Jun 26 '18 at 10:19

The node names inside MATCH can be repeated. In other words, a node can be traversed an arbitrary number of times in the same query. An edge name cannot be repeated inside MATCH. An edge can point in either direction, but it must have an explicit direction. OR and NOT operators are not supported in the MATCH pattern. MATCH can be combined with other expressions using AND in the WHERE clause. However, combining it with other expressions using OR or NOT is not supported. Find 2 User who are both friends with same User

SELECT Person1.name AS Friend1, Person2.name AS Friend2
FROM user user1, friend friend1, user user2,
friend friend2, user user0
WHERE MATCH(user1-(friend1)->user0<-(friend2)-user2);

this pattern can also be expressed as below

SELECT user1.name AS Friend1, user2.name AS Friend2
FROM user user2, friend friend1, user user2,
friend friend2, user user0
WHERE MATCH(user1-(friend1)->user0 AND user2-(friend2)->user0);

thanks - it right - but maybe user1 and user2 have direct edge between together. i want they dont have direct edge between they. — mhsankar, Jun 26 '18 at 10:22

How to select from SQL Server Graph Table When Where Clause Not Match

1 Answers1