How to return nodes where none of related nodes have a certain quality

I have Company nodes which are related to User nodes and Users have the plan_type trait. A Company could have multiple Users and Users could have different plan_types from one another.

I need to return a list of Company where none of the Users have a certain plan type.

MATCH (c:Company)-[:CLAIMED_BY]-(u:User)
WHERE NOT u.plan_type in ['Beginner','Intermediate']
RETURN c.name

But this way, if a Company has three Users with the plans Beginner, Intermediate, and Advanced, it will still return the Company because of the Advanced User.

There's a few ways to get to this, but here is one that might work:

MATCH (c:Company)-[:CLAIMED_BY]-(u:User)
WITH c, collect(DISTINCT u.plan_type) AS c_plans
WHERE NOT 'Beginner' IN c_plans AND NOT 'Intermediate' IN c_plans
RETURN c.name

1 Like