Complex Match Statement

[mithun_das]

I have a relationship like following

(country)<-[:REGION_OF]-(region)<-[:CITY_OF]-(city)
AND
(country)<-[:CITY_OF]-(city)
Now i use the following query to get the all the city and country which have or does not have a relationship with country.
(country)-[*]-(city)
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.

[koji]

Hi,

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 country.name AS country,
       region.name AS region,
       city.name AS city

[mithun_das]

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

[mithun_das]

Its working very well

[andrew_bowman · Accepted solution]

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

(country)<-[:REGION_OF]-(region)<-[:CITY_OF]-(city)
and not this pattern
(country)<-[:CITY_OF]-(city)

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 country.name as country, CASE WHEN length(path) = 2 THEN nodes(path)[1].name ELSE null END as region, city.name as city

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

[mithun_das]

Hi, Sorry for the late reply...

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

[mithun_das]

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....