cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.

🌧 Create Relationship Between 3 tables

mojtaba_tmj
Node Clone

Is it possible to create relationships between table1 with table3 by using table2?
for example in:
--Table1 we have id and momName
--Table2 we have id and table1ID and Table3ID
--Table3 we have id and dadName
we need to create relationship between mom and dad directly by just one type of relationship family without showing the table2 nodes.

7 REPLIES 7

ameyasoft
Graph Maven

Hi,
Assuming

T1.csv:
ID,MomName
1,Mom1

CREATE (m:Mom {id: 1, name:"Mom1"})

T3.csv:
ID,DadName
1,Dad1

CREATE (m:Dad {id: 1, name:"Dad1"})

T2.csv:
ID,T1ID,T2ID
1,1,1
2,2,2

LOAD T2.csv....
MATCH (m:Mom) WHERE m.id = T1ID
MATCH (d:Dad) WHERE m.id = T1ID
MERGE (m)-[:FAMILY]->(d)

If the tables are in SQL Server, then you can use this query to generate one file for import.

select a.ID, b.ID as MomID, b.MomName, c.ID as DadID, c.DadName
from Table2 a
inner join Table1 b on b.ID = a.MomID
inner join Table2 c on c.ID = a.DadID

This results in:
2X_d_d4bad5d30a2157cb6e42b9596146d2f270977262.png

Import the data to create nodes and relationships:
CREATE (m:Mom {id: 1, name:"Mom1"})
CREATE (m:Dad {id: 1, name:"Dad1"})
MERGE (m)-[:FAMILY]->(d)

-Kamal

Hi,

Sorry for the typo:

LOAD T2,csv should read

LOAD T2.csv....
MATCH (m:Mom) WHERE m.id = T1ID
MATCH (d:Dad) WHERE m.id = T2ID
MERGE (m)-[:FAMILY]->gt;(d)

-Kamal

mojtaba_tmj
Node Clone

thanks for the idea but what about millions of records.
I mean if we have a dad who has 40 mom(wife) relationships. by this theory, our unique table will be infinity and each query need lot's of time even if our computer can handle that.

what do you think about this one:

MATCH (t2:Table2)
MATCH (t1:Table1) WHERE t1.id in t2.momID
MATCH (t3:Table3) WHERE t3.id in t2.dadID
MERGE (p)<-[:FAMILY]->(a)

Yes, this should work.
MERGE (t1)<-[:FAMILY]->(t3)

-Kamal

I stand corrected. This approach may create some wrong relationships between mom and dad and may not follow the relations defined in Table2.

-Kamal

I did some testing with sample data.

Here is the Cypher script:
CREATE (m:Mom {id:1, name:"Mom1"})
CREATE (m1:Mom {id:2, name:"Mom2"})
CREATE (m2:Mom {id:3, name:"Mom3"})
CREATE (m3:Mom {id:4, name:"Mom4"})

CREATE (m4:Dad {id:1, name:"Dad1"})
CREATE (m5:Dad {id:2, name:"Dad2"})
CREATE (m6:Dad {id:3, name:"Dad3"})
CREATE (m7:Dad {id:4, name:"Dad4"})

CREATE (r:Rel {id:1, momID:1, dadID:1})
CREATE (r1:Rel {id:2, momID:2, dadID:2})
CREATE (r2:Rel {id:3, momID:3, dadID:3})
CREATE (r3:Rel {id:4, momID:4, dadID:4})

CREATE (r4:Rel {id:5, momID:2, dadID:1})
CREATE (r5:Rel {id:6, momID:2, dadID:3})
CREATE (r6:Rel {id:7, momID:1, dadID:3});

Ran the following query to create the relationships:
MATCH (r:Rel)
MATCH (m:Mom) WHERE m.id in r.momID
MATCH (d:Dad) WHERE d.id in r.dadID
MERGE (m)-[:FAMILY]->(d)
RETURN m, d;

and the result is:
2X_2_2a23e7c3ce02d7afe2286afa4cb3bf8d0a1019dc.png

Looks like it's working as expected. Hope this will work for you.
-Kamal

With millions of rows:

make sure you have:

  1. indexes / constraints for :Person(id)
  2. use: USING PERIODIC COMMIT to batch writes.