cancel
Showing results for 
Search instead for 
Did you mean: 

How to join two match statements?

marcelix161
Node Clone

The following query returns the error: variable not defined.

MATCH (n:ID {start_marker: 1})
WITH DISTINCT collect(n.CASE_ID) AS start_marker
MATCH (m:ID)
WITH DISTINCT collect(m.CASE_ID) AS all
CALL apoc.coll.subtract(all, start_marker)

I understood that the with keyword would pass the variable to the next statement, but I don't get it quite right.
How can I join two match statements results and then compare them with e.g. some apoc function?

1 ACCEPTED SOLUTION

glilienfield
Ninja
Ninja

Your query has a few syntax errors in it. Try the following:
MATCH (n:ID {start_marker: 1})
WITH collect(DISTINCT n.CASE_ID) AS start_marker
MATCH (m:ID)
WITH start_marker, collect(DISTINCT m.CASE_ID) AS all
RETURN apoc.coll.subtract(all, start_marker)

In particular, the start_marker variable is not in scope in your query when it is referenced by the apoc function. You need to pass it in the 2nd WITH clause too. The apoc.coll.subtract is a function, so you don't use the CALL clause; you just call the function directly. Finally, the DISTINCT clause goes inside the collect method as part of its parameter. As a note, the apoc function seems to remove duplicates from the lists, so the DISTINCT clauses should not be necessary.

As an alternative approach, the following should provide the same result without depending on the apoc library:

MATCH (n:ID {start_marker: 1})
WITH collect(DISTINCT n.CASE_ID) AS start_marker
MATCH (m:ID)
WHERE NOT m.CASE_ID in start_marker
RETURN collect(DISTINCT m.CASE_ID) AS all

View solution in original post

2 REPLIES 2

andy_hegedus
Graph Voyager

Hi,

Have you looked at the union function? Is it sufficient

Andy

glilienfield
Ninja
Ninja

Your query has a few syntax errors in it. Try the following:
MATCH (n:ID {start_marker: 1})
WITH collect(DISTINCT n.CASE_ID) AS start_marker
MATCH (m:ID)
WITH start_marker, collect(DISTINCT m.CASE_ID) AS all
RETURN apoc.coll.subtract(all, start_marker)

In particular, the start_marker variable is not in scope in your query when it is referenced by the apoc function. You need to pass it in the 2nd WITH clause too. The apoc.coll.subtract is a function, so you don't use the CALL clause; you just call the function directly. Finally, the DISTINCT clause goes inside the collect method as part of its parameter. As a note, the apoc function seems to remove duplicates from the lists, so the DISTINCT clauses should not be necessary.

As an alternative approach, the following should provide the same result without depending on the apoc library:

MATCH (n:ID {start_marker: 1})
WITH collect(DISTINCT n.CASE_ID) AS start_marker
MATCH (m:ID)
WHERE NOT m.CASE_ID in start_marker
RETURN collect(DISTINCT m.CASE_ID) AS all