I'm trying to retrieve all friends of a particular person using SQL Server graph processing
Person table as node
╔════╦═══════╗ ║ Id ║ Name ║ ╠════╬═══════╣ ║ 1 ║ David ║ ║ 2 ║ Suraj ║ ║ 3 ║ John ║ ║ 4 ║ Ryan ║ ╚════╩═══════╝
likes as an edge//for simplicity I am not using auto-generated ids here
╔════╦═══════╦═══════╦═══════════════════╗ ║ Id ║ From ║ To ║ Remarks ║ ╠════╬═══════╬═══════╬═══════════════════╣ ║ 1 ║ David ║ Suraj ║ David likes Suraj ║ ║ 2 ║ David ║ John ║ David likes John ║ ║ 3 ║ John ║ Ryan ║ John likes Ryan ║ ╚════╩═══════╩═══════╩═══════════════════╝
My graph query to find all friends of John would be like this
select p1.name, p2.name [friend]
from person p1, likes l, person p2
where p1.name = 'John' and match(p1-(l)->p2)
and this will return the below result set
╔══════╦════════╗ ║ name ║ friend ║ ╠══════╬════════╣ ║ John ║ Ryan ║ ╚══════╩════════╝
The problem is we got all the people John likes, which excludes the people who likes John(in this case David). In real world, if a person is a friend of me, I am also a friend of him right?. I know I can use union here to find all people who likes John and add up to the above. But It will make the case worst for scenarios where finding friend of friends. Can we do it more intuitively tweaking with the Match or the arrows
Expected Result
+------+--------+ | Name | Friend | +------+--------+ | John | Ryan | | John | David | +------+--------+
update: expected result added
SQL Server graph is directional. So you
MATCH doesn't allow OR, you would have to use UNION of two queries where each uses the directionality differently:select p1.name, p2.name [friend]
from person p1, likes l, person p2
where p1.name = 'John' and match(p1-(l)->p2)
UNION
select p2.name, p1.name [friend]
from person p1, likes l, person p2
where p2.name = 'John' and match(p1-(l)->p2)