I want to establish a relation between <id> of Department and id_dept of Staff where <id> is mapping to id_dept - 1
ChatGPT gives me
MATCH (dept:Department)
MATCH (staff:Staff)
WHERE staff.id_dept - 1 = dept.id
MERGE (staff)-[:WORKS_IN]->(dept)
RETURN dept, staff;
but it doesn't work. I then tried to create another property in department as id_dept hopping that I can map both id_dept in both nodes but that didn't work either
I want to basically that the staff with id_dept == 1 WORKS_IN department with <id> 0
Thanks for the help in advance
actually these are imported from sql. id_dept was a foreign key in staff mapping to id of department table
Then it would seem this is correct.
MATCH (dept:Department)
MATCH (staff:Staff)
WHERE staff.id_dept = dept.id
MERGE (staff)-[:WORKS_IN]->(dept)
RETURN dept, staff;
Note, you should create the relationship when you import the nodes.
It creates no relationship
sample dept node
<elementId>: 4:40fdb23c-87fe-4860-ab6f-59fd80d7e7e6:15
<id>: 15
DEPT_HEAD: Sophia Lopez
DEPT_NAME: Ophthalmology
EMP_COUNT: 5
sample staff node
<elementId>: 4:40fdb23c-87fe-4860-ab6f-59fd80d7e7e6:42
<id>: 42
ADDRESS: New Sarahberg, IA 31150"
DATE_JOINING: 25-08-18
EMAIL: emily66@example.net
EMP_FNAME: Kenneth
EMP_ID: 16
EMP_LNAME: Ayers
IS_ACTIVE_STATUS: Y
SSN: 719506165
id_dept: 25
I dropped both nodes and recreated them together with relationship cypher at the end
Also, shouldn't it be
WHERE staff.id_dept - 1 = dept.id instead of WHERE staff.id_dept = dept.id?
creation code:
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/anaritaasp/nosql-projeto/neo4j/hospital-csv/department.csv/DEPARTMENT_DATA_TABLE.csv?token=GHSAT0AAAAAACPXCOWHY67BHMVSU3LJEO2UZROE3UQ' AS row
CREATE (:Department {
DEPT_HEAD: row.DEPT_HEAD,
DEPT_NAME: row.DEPT_NAME,
EMP_COUNT: toInteger(row.EMP_COUNT)
});
// Load staff CSV data
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/anaritaasp/nosql-projeto/neo4j/hospital-csv/staff/STAFF_DATA_TABLE.csv?token=GHSAT0AAAAAACPXCOWHSAJSUHFZKX6UILUOZROEZ3A' AS row
// Create staff nodes
CREATE (:Staff {
EMP_ID: toInteger(row.EMP_ID),
EMP_FNAME: row.EMP_FNAME,
EMP_LNAME: row.EMP_LNAME,
DATE_JOINING: row.DATE_JOINING,
DATE_SEPERATION: row.DATE_SEPERATION,
EMAIL: row.EMAIL,
ADDRESS: row.ADDRESS,
SSN: toInteger(row.SSN),
IS_ACTIVE_STATUS: row.IS_ACTIVE_STATUS,
id_dept: row.IDDEPARTMENT
});
MATCH (dept:Department)
MATCH (staff:Staff)
WHERE staff.id_dept = dept.id
MERGE (staff)-[:WORKS_IN]->(dept)
RETURN dept, staff;
where do you create the Department nodes?
ameyasoft
April 28, 2024, 4:22pm
7
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/anaritaasp/nosql-projeto/neo4j/hospital-csv/department.csv/DEPARTMENT_DATA_TABLE.csv?token=GHSAT0AAAAAACPXCOWHY67BHMVSU3LJEO2UZROE3UQ' AS row
CREATE (:Department {
DEPT_HEAD: row.DEPT_HEAD,
DEPT_NAME: row.DEPT_NAME,
EMP_COUNT: toInteger(row.EMP_COUNT)
});
Above Cypher is missing "dept.id" property. Check
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/anaritaasp/nosql-projeto/neo4j/hospital-csv/department.csv/DEPARTMENT_DATA_TABLE.csv?token=GHSAT0AAAAAACPXCOWHY67BHMVSU3LJEO2UZROE3UQ' AS row
CREATE (:Department {
DEPT_HEAD: row.DEPT_HEAD,
DEPT_NAME: row.DEPT_NAME,
EMP_COUNT: toInteger(row.EMP_COUNT)
});
before creating staff node
but when you create department node. It creates you an <id>. See the node
<elementId>: 4:40fdb23c-87fe-4860-ab6f-59fd80d7e7e6:15
<id>: 15
DEPT_HEAD: Sophia Lopez
DEPT_NAME: Ophthalmology
EMP_COUNT: 5
here <id> for this node is 15
Ok, I see. You are relying on the node id. You should not do that. Each department should have an id you assign. This is the key you would use to link an employee to a department. Right now you are not creating an id in your cypher to create the department.
