Joining results from two subqueries

[iuliana-laurent]

Hi Everyone!

Given the following db schema:

I'm trying to fetch all parents, children and the respective properties of both parents and children in the following format:

parent | child | parent_property | child_property

I'm using the UNION clause to gather the the parents and children from different nodes under the same columns. The first CALL {} subquery is working as expected. However, when I add the second one to return the properties of children and parents, I obtain duplicated results. I assume the issue is in the way I try to combine the results of both subqueries.

Here is the Cypher script:

//Getting children and parents from different nodes in the same columns
CALL { 
    MATCH (c:subPART)<-[:Has_child]-(p:mainPART {id: "12345"}) RETURN c.id   AS child, p.id AS parent
    UNION
    MATCH (m:mainPART )<-[:Has_child]-(p:mainPART {id: "12345"}) RETURN m.id AS child, p.id AS parent
   
} 
//Trying to assign resprective property for parent 
MATCH (p)-[]->(r)-[:Has_property]->(property1) 
//Trying to assign resprective property for the children
CALL {
    MATCH (c)-[]->(k)-[:Has_property]->(property2) RETURN property2.name AS child_property
    UNION
    MATCH (m)-[]->(n)-[:Has_property]->(property3) RETURN property3.name AS child_property
}
RETURN parent, child, property1.name AS parent_property, child_property

I'm using neo4j Desktop version 4.3.3.

Any tips would be very much appreciated! Thank you!

[giuseppe_villan]

@iuliana-laurentia

I think your query doesn't work because (c) and (m) in the second CALL are not the c and m from the first query, because you return c.id AS child, p.id AS parent, you should return even c and m.

From your data model, is not clear where is Has_property relationships,
anyway maybe this query should work (without second call😞

CALL { 
    MATCH (c:subPART)<-[:Has_child]-(p:mainPART {id: "12345"}) RETURN c as child, p AS parent
    UNION
    MATCH (m:mainPART )<-[:Has_child]-(p:mainPART {id: "12345"}) RETURN m AS child, p AS parent
} 
MATCH (parent)-[]->(r)-[:Has_property]->(property1) 
MATCH (child)-[]->(k)-[:Has_property]->(property2)
return child.id as child, parent.id as parent, property2.name AS child_property, property1.name AS parent_property

In case it doesn't work, can you provide some queries to replicate your dataset?