Create Relations between two existing nodes 1:N (no CSV)

[stlr]

I created two sets of nodes: person and location. Now I want to relate every person to one or many locations (1:N). A person is matching to a location where personPK = FKperson. I used the following cypher scripts but neither of them worked.

// script A
MATCH (p:Person {personPK: personPK})
MATCH (l:Location {FKperson: FKperson})
MERGE (p)-[rel: VISITED]->(l)
RETURN count(rel);

// script B
MATCH (p: Person), (l: Location)
WHERE p.personPK = l.FKperson
MERGE (p)-[rel: VISITED]->(l)
RETURN count(rel);

Couldn't find a thread about this, seems to be to simple. But I would appreciate some help, thanks.

[koji]

Hi @stlr

I don't think script A works, but script B works fine.
The Cypher code have been reformatted with PyCharm.

First, I created the small data.

CREATE (:Person {personPK: 1})
CREATE (:Person {personPK: 2})
CREATE (:Person {personPK: 3})
CREATE (:Person {personPK: 4})
CREATE (:Person {personPK: 5})
CREATE (:Location {FKperson: 1})
CREATE (:Location {FKperson: 3})
CREATE (:Location {FKperson: 5})

And Check the relations.

MATCH (p:Person), (l:Location)
  WHERE p.personPK = l.FKperson
RETURN p, l

And Run script B. (count(rel) is 3)

MATCH (p:Person), (l:Location)
  WHERE p.personPK = l.FKperson
MERGE (p)-[rel:VISITED]->(l)
RETURN count(rel);

[stlr]

Thanks @koji
But you created a relationship 1:1 and my challenge is a 1:N. Meaning: one Person can have visited more than one location. This might be the cause why my script B isn't working.
I also tried the following script C, but it just generates new nodes in relation 1:1. The task to connect existing nodes 1:N according to PK/FK is not performed.

MATCH (p:Person)
MERGE (o:Location { FKperson: p.personPK })
MERGE (p)-[rel:VISITED]->(o)
RETURN p.personPK, p.Name, o.CityName, o.Date, o.FKperson

[koji]

@stlr

If you write ３ MERGE as shown below, ３ nodes will be created.
So, I think FKperson should not be included in the location.

MERGE (:Location { FKperson: 1 })
MERGE (:Location { FKperson: 2 })
MERGE (:Location { FKperson: 3 })

I think you need (:Relation) node like this

CREATE (:Person {personPK: 1})
CREATE (:Person {personPK: 2})
CREATE (:Person {personPK: 3})
CREATE (:Person {personPK: 4})
CREATE (:Person {personPK: 5})

CREATE (:Location {City: "New York"})
CREATE (:Location {City: "Los Angeles"})
CREATE (:Location {City: "Chicago"})

CREATE (:Relation {FKperson: 1, City: "New York"})
CREATE (:Relation {FKperson: 3, City: "New York"})
CREATE (:Relation {FKperson: 5, City: "Chicago"})

MATCH (r:Relation), (p:Person), (o:Location)
  WHERE p.personPK = r.FKperson
  AND o.City = r.City
MERGE (p)-[rel:VISITED]->(o)

[stlr · Accepted solution]

Thanks @koji
I found my mistake. As I imported the data for the location nodes I forgot to change the format of FKperson to integer like this
"toInteger(row[5]) AS FKperson"

After I changed the format of FKperson the following script worked well, even for the 1:N relationship:

MATCH (p: Person), (o: Location)
WHERE p.personPK = o.FKperson
MERGE (p)-[rel: VISITED]->(o)
RETURN count(rel)

[clem]

I'm confused as to what is wanted here.

What/where is the data that would allow you to connect the nodes?

You seem to only have data that would allow you to make just the connections you showed based on this relationship:

p.personPK = l.FKperson

I feel there is something you're not telling us.