0

I need to model a graph in SQL Server using its Graph extensions. Say I have three tables - persons, restaurants and cities. I have one edge table, called "in", a person or a restaurant can be in a city.

Now I want to retrieve all things in New York. In Cypher I could do something like

MATCH (a)-[:in]-(b:city {name: "New York")
RETURN a

Is it possible to do the same in SQL Server with its graph extensions? As far as I could tell, I should do something like

SELECT Person.name
FROM Person In City
WHERE MATCH (Person)-(in)-(City) AND city.name='New York'
UNION
SELECT Restuarant.name
FROM Restaurant In City
WHERE MATCH (Restuarant)-(in)-(City) and city.name='New York'

That can become very verbose when I have 8 different entity types at are in cities.

Is there a way around this?

zmbq
  • 38,013
  • 14
  • 101
  • 171
  • You can have a supertable of `Entity (id, name)` which the other tables are foreign-keyed to in 1:1 relationship https://stackoverflow.com/a/2003042/14868997 – Charlieface Jun 07 '23 at 22:09
  • good idea, but how do I query (entity)-(in)-(city|restaurant)-(in)-(city) ? – zmbq Jun 08 '23 at 06:43
  • Well it will just be entity-in-city. You can use a `type` column on the base table to differentiate the different entities, and filter based on that if you need – Charlieface Jun 08 '23 at 09:17

0 Answers0