Title: Exclude nodes based on other relationship
Tags: neo4j;cypher
Question: I am trying to get a result of Users and Accounts that are simultaneously ```:USER``` and ```:ADMIN``` of Accounts that does not have the ```:PARENT``` relation to or from any other account. 

Background:

This gives me all accounts that are neither parent accounts nor child accounts:

```MATCH (a: Account) 
WHERE NOT (a)-[:PARENT]-()
RETURN a
```

This gives me all accounts where all Users also have the ```:ADMIN``` relation to an Account:

```MATCH (u)-[:ADMIN]-(a)-[:USER]-(u)
RETURN a, u
```

Problem:

When trying to combine the two, I still get Accounts with ```:PARENT``` relations in my results:

```MATCH (u)-[:ADMIN]-(a)-[:USER]-(u)
WHERE NOT (a)-[:PARENT]-()
RETURN u, a
```

It's like the ```WHERE``` on row 2 here does not have any effect. It is possible that the unwanted Accounts show up because they match the first ```MATCH``` but from here I would like to exclude all Accounts that has any ```:PARENT``` relation.

What I have tried

I have been trying using ```OPTIONAL MATCH```, the ```WITH``` keyword and matching in different orders and variations for the criteria. The three snippets above is the closest I can get to describing where it goes wrong.

Another way to approach this I imagine could be

```MATCH (a: Account) 
OPTIONAL MATCH (a)-[p:PARENT]-()
WITH a, p 
WHERE p IS NULL 
OPTIONAL MATCH (u)-[relu: USER]-(a)-[rela: ADMIN]-(u)
with u, a, relu, rela
WHERE NOT relu IS NULL
AND NOT rela IS NULL
RETURN a, u
```

Here I get too few results. It's missing several accounts that are not having any ```:PARENT``` relation but still has Users with both ```:USER``` and ```:ADMIN``` relations to the account.

Seems like I missed some vital point of how the queries are aggregated.

Comment: Is the direction of the relationships not relevant? That is, `WHERE NOT (a)-[:PARENT]-()` could be formulated as `WHERE NOT (a)-[:PARENT]->()` or reverse, based on your model.

Comment: Okay. Another minor thing that I noticed is that label for `a`, `(a:Account)` is omitted from the 2nd and 3rd queries. I guess it should not make a difference, but still worth noting.

Comment: The `PARENT` relation has a direction but I exclude both incoming and outgoing to get "all accounts that are neither parent accounts nor child accounts".

Comment: I am assuming a variable like `a` is only needed to be declared once, as it is the same object I am referring?

Here is the accepted answer: (This answer is work-in-progress.)

I created a small example data set:

```CREATE
  (a:Account {name: 'acc1'}),
  (u:User {name: 'user1'}),
  (u)-[:ADMIN]-&gt;(a),
  (u)-[:USER]-&gt;(a)
```

This query seems to work. The only difference from you 3rd query is the addition of ```:Account```.

```MATCH (u)-[:ADMIN]-(a:Account)-[:USER]-(u)
WHERE NOT (a)-[:PARENT]-()
RETURN u, a
```

Comment for this answer: Sure. The best is to craft a small example dataset that demonstrates the issue. Alternatively, you can also Twitter DM/email me a zipped graph.db or a GraphML file if its not confidential.
Comment for this answer: If it's not too large, you can export it to a GraphML/CSV and share it on a GitHub Gist, pastebin or similar. Alternatively, you can upload the zip somewhere (maybe Dropbox or similar)
Comment for this answer: Oh wow! Ok so I see now that that declaration does make a difference here! Why is that? These relations only point at Accounts anyway.
Comment for this answer: Running query # 3 with the Account declaration gives me the same results as my alternative query # 4 though. Which I cannot understand why it is wrong. Maybe I can include an example db somehow to show this...?
Comment for this answer: not confidential. This "craft a small example dataset" - How would I best share that inside this SO question?
Comment for this answer: Solved, thanks. My 3rd query gave unexpected results because of not labeling enough. (Still beats me,) My 4th query gave unexpected, but correct (same as yours), results because it also sorts out those Accounts who has Users who are related to more than one account.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:11/14/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<neo4j><cypher>', 'Title': 'Exclude nodes based on other relationship', 'ViewCount': 51, 'id': 47293772, 'user_AboutMe': '', 'user_AccountId': 85110.0, 'user_DisplayName': 'JOG', 'user_Reputation': 5530.0, 'user_Views': 305.0, 'user_id': 237509.0}}