Getting all nodes related to an specific node but not to another

Question

Having this model:

(:Person)-[:has]-(:Movie)

I need to get all the movies that an arbitrary group of people have but another one does not.

I started with:

MATCH (p1.Person {Name: 'Josh'})-[:has]->(m:Movie)
WHERE not exists ((p2:Person {Name: 'Carl'})-[:has]->(m))
RETURN COUNT(m)

And I get the number of movies that p1 has and p2 does not. The problem is that I need the same with groups, not individuals; something like:

MATCH (p1.Person {Name: ['Josh','Mark]})-[:has]->(m:Movie)
WHERE 
not exists ((p2:Person {Name: ['Carl','Roger']})-[:has]->(m))
RETURN COUNT(m)

I would need an equivalent query that returns the movies that Josh AND Mark have and Carl AND Roger don't.

Any sugestion?

Do you need to consider the movie that Josh has, but which Mark, Carl and Roger do not have? — stdob--, Jul 14 '17 at 21:21

score 2 · Accepted Answer · answered Jul 14 '17 at 18:53

If you pass your person-name lists as 2 parameters (say, yes and no), you can avoid modifying the Cypher code for different combinations of names.

For example:

MATCH (p1:Person)-[:has]->(m1:Movie)
WHERE p1.Name IN $no
WITH COLLECT(DISTINCT m1) AS blacklist
MATCH (p2:Person)-[:has]->(m2:Movie)
WHERE p2.Name IN $yes AND NOT (m2 IN blacklist)
RETURN COUNT(DISTINCT m2);

The DISTINCT keyword is used to eliminate duplicates.

score 1 · Answer 2 · answered Jul 14 '17 at 13:17

You can use the NONE predicate :

MATCH (person:Person) WHERE person.Name IN ['Carl', 'Roger']
WITH collect(person) AS blacklist
MATCH (p1:Person)-[:has]->(m:Movie)
WHERE p1.Name IN ['Josh', 'Mark']
AND NONE 
    ( p2 IN blacklist
         WHERE EXISTS( (p2)-[:has]->(m)) 
    )
RETURN COUNT(m)

score 0 · Answer 3 · answered Jul 14 '17 at 13:17

You can try something like this:

// Match p1 (Josh and Mark)
MATCH (p1:Person)
WHERE p1.name in ['Josh','Mark']
// Match p2 (Carl and Roger)
MATCH (p2:Person)
WHERE p2.name in ['Carl','Roger']
// Match movies that p1 group have but p2 does not
MATCH (p1)-[:has]->(m:Movie)
WHERE NOT (p2)-[:has]->(m:Movie)
// return the count
RETURN COUNT(m)

Getting all nodes related to an specific node but not to another

3 Answers3