Excluding relationships with different values of the same property

[guido]

It's probably something simple that I am overlooking, but I can't find it....😀
I have a database of people (p:Producer) with certain career events, e.g. "apprentice" and "master". I want to create a table that shows all the data for people that have been registered a apprentices but never made it to master.
I've tried variants of the following (simplified): 

MATCH (p:Producer)-[r:CAREERSTEP {step: 'apprentice'}]->(c:CareerEvent)
WHERE NOT (p:Producer)-[r:CAREERSTEP {step: 'master'}]->(c:CareerEvent)
RETURN p.ProducerID

I also tried different variations/combinations of OPTIONAL MATCH, IS NULL, exists() and alike but I haven't found anything that worked.
Any suggestions will, as always, be greatly appreciated.

[glilienfield · Accepted solution]

Try this:

MATCH (p:Producer)-[r:CAREERSTEP {step: 'apprentice'}]->(c:CareerEvent)
WHERE NOT EXISTS( (p)-[:CAREERSTEP {step: 'master'}]->(:CareerEvent) )
RETURN p.ProducerID

Can your provide some same data if it does not work, so I can understand you model better and test approaches?

[glilienfield · Accepted solution]

Try this:

MATCH (p:Producer)-[r:CAREERSTEP {step: 'apprentice'}]->(c:CareerEvent)
WHERE NOT EXISTS( (p)-[:CAREERSTEP {step: 'master'}]->(:CareerEvent) )
RETURN p.ProducerID

Can your provide some same data if it does not work, so I can understand you model better and test approaches?

[guido]

Hi,
that does the job!
I didn't know that there was such a huge difference between using r:CAREERSTEP and :CAREERSTEP .
Can you point me to a location in the manual/elsewhere where I can read more about this? Thanks!!!

[glilienfield · Accepted solution]

There is not much in the docs about variables used in patterns. A variable is defined in a pattern the first time it is used. It binds the entity (node or relationship) to the variable, so the entity can be referenced in elsewhere in the query, Variables are scoped only in the part part they are defined in, unless they are pasted to the next query part via a 'with' clause. 

In your query, you continued to use the variables in the 'not' pattern. This constrained the pattern to those entities bound to the variables. As such, your 'not' pattern is asking if there does not exists a path through the same entities, p, r, and c, where the 'step' property of the relationships is now equal to 'master.' Since this relationship 'r' has a 'stage' property equal to 'apprentice' (due to the match crtieria), then this will always be true for each match.  As such, I suspect you were getting all paths for apprentices, regardless of the 'master' step. 

I removed the variables in my query so the 'where' pattern would not be constraint to the same entities in the match statement, except the 'p' node. this let's the query look for other relationships origination from the 'p' node that traverse through a different CAREERSTEP relationship with 'step' property equal to 'master', which terminates on any 'CareerEvent' entity. This does what you want.

Hope that helps. 

https://neo4j.com/docs/cypher-manual/current/syntax/variables/

https://neo4j.com/docs/cypher-manual/current/syntax/patterns/

[guido]

That clarifies a lot, thanks a million (again)!
Pity that this is not explained in the docs... maybe something can be done about that, @michael_hunger ?

@michail_hun