Complex Match Statement

I have a relationship like following

Now i use the following query to get the all the city and country which have or does not have a relationship with country.
and it is giving me the country of the city which is directly related with country and also all the cities.
But I want to get a list like following

country | region | city
c1 | null | ct1
c2 | r1 | ct2
c3 | r2 | ct3
c4 | null | ct4

Is this possible, can someone help regarding this.


First, we created the test data.
Is this correct?

CREATE (:Country {name: 'c1'})<-[:CITY_OF]-(:City {name: 'ct1'}),
       (:Country {name: 'c2'})<-[:REGION_OF]-(:Region {name: 'r1'})<-[:CITY_OF]-(:City {name: 'ct2'}),
       (:Country {name: 'c3'})<-[:REGION_OF]-(:Region {name: 'r2'})<-[:CITY_OF]-(:City {name: 'ct3'}),
       (:Country {name: 'c4'})<-[:CITY_OF]-(:City {name: 'ct4'})

The trick is to use OPTIONAL MATCH.

MATCH (country:Country)<-[*]-(city:City)
OPTIONAL MATCH (country)<-[:REGION_OF]-(region:Region)<-[:CITY_OF]-(city)
RETURN AS country, AS region, AS city

1 Like

Can you confirm that if the city is part of a region, that only this pattern exists for it:

and not this pattern

We basically want to make sure we don't count cities twice if both patterns exist for a city.

If that holds true, then we can do something like this, recognizing that only a 3-node path contains the region.

MATCH path = (country:Country)<-[:REGION_OF | CITY_OF*]-(city:City)
RETURN as country, CASE WHEN length(path) = 2 THEN nodes(path)[1].name ELSE null END as region, as city

Whoops, I put the wrong length check in there, length is based on number of relationships, not nodes. Query fixed.

1 Like

Hi, Sorry for the late reply...

Yes I confirm that so your solution works... Will definitely try it.

Hi Sir, Sorry for the late reply.... I will try this out.

Its working very well

Its working.... basicall both the solution are working for me.... marked @andrew.bowman as solution as it really short, while @koji 's solution is also working very well....