# Retrieve nodes based on relationships count with an exception

**URL:** <https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756>\
**Category:** Graph Data Science / Graph Analytics\
**Tags:** cypher\
**Created:** [August 16, 2023, 10:03am UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756 "2023-08-16T10:03:27Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![yanivts1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yanivts1/32/29440_2.png) [@yanivts1](https://community.neo4j.com/u/yanivts1)\
**Post date:** [August 16, 2023, 10:03am UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/1 "2023-08-16T10:03:27Z")

</div>

Hi,  
I have 100000 nodes "Person" and 1000 "Company"  
i have relationship types "CEO" , "CTO", "ACCOUNTANT", "WORKER", ....  
A person can be related to several companies with all type of rels...  
Now, i need to find the top 10 Persons with most connections **Except**"ACCOUNTANT"  
for example:  
John is a CEO in 4 companies, CTO in 10 companies and ACCOUNTANT in 20 companies  
Jane is CEO in 2 companies, CTO in 10 companies and ACCOUNTANT in 40 companies  
We see that Jane has more relationships than John but... if we ignore "ACCOUNTANT" , John has more relationships than Jane  
The query should return the top 10 Persons with all relationships (except "ACCOUNTANT") to the companies (including the Company nodes)  
Thanks

---

<div class="post-metadata">

**Author:** ![v2belleville](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/v2belleville/32/29050_2.png) [@v2belleville](https://community.neo4j.com/u/v2belleville)\
**Post date:** [August 16, 2023, 12:14pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/2 "2023-08-16T12:14:48Z")

</div>

if I get you right, what you need is the top 10 of a list of number of relations between a person and a company (excluding accountant relations)

did not test but it should be something like

MATCH (p:Person)-[r:CEO|CTO|WORKER]-(:Company)  
WITH p, size(collect(r)) as nbRel  
return p order by nbRel desc limit 10

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [August 16, 2023, 12:21pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/3 "2023-08-16T12:21:48Z")

</div>

Try this. The relationship expression syntax requires version 5.

> **[Expressions - Cypher Manual](https://neo4j.com/docs/cypher-manual/current/syntax/expressions/#relationship-type-expressions)**
>
> This section contains an overview of expressions in Cypher with examples.

```auto
Match (n:Person)
with n, count{(n)-[:!ACCOUNTANT]-(:Company)} as cnt
Order by cnt desc
Limit 10
Return n

```

---

<div class="post-metadata">

**Author:** ![v2belleville](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/v2belleville/32/29050_2.png) [@v2belleville](https://community.neo4j.com/u/v2belleville)\
**Post date:** [August 16, 2023, 12:36pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/4 "2023-08-16T12:36:30Z")

</div>

hi Gary, never saw the count{} syntax, can you please point to a documentation reference about it ?

---

<div class="post-metadata">

**Author:** ![yanivts1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yanivts1/32/29440_2.png) [@yanivts1](https://community.neo4j.com/u/yanivts1)\
**Post date:** [August 16, 2023, 12:42pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/5 "2023-08-16T12:42:42Z")

</div>

Thanks, but i have more than 20 relationship types' and i don't want to specify all of them except ACCOUNTANT, i want to specify only the exclusion

---

<div class="post-metadata">

**Author:** ![v2belleville](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/v2belleville/32/29050_2.png) [@v2belleville](https://community.neo4j.com/u/v2belleville)\
**Post date:** [August 16, 2023, 12:43pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/6 "2023-08-16T12:43:54Z")

</div>

then use the syntax Gary suggested : [:!ACCOUNTANT]

---

<div class="post-metadata">

**Author:** ![yanivts1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yanivts1/32/29440_2.png) [@yanivts1](https://community.neo4j.com/u/yanivts1)\
**Post date:** [August 16, 2023, 12:49pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/7 "2023-08-16T12:49:12Z")

</div>

> [@glilienfield](#):
>
> ```auto
> Match (n:Person)
> with n, count{(n)-[:!ACCOUNTANT]-(:Company)} as cnt
> Order by cnt desc
> Limit 10
> Return n
> 
> ```

Hi, Thanks, but it returns only the persons, and i already managed to do it.  
What I need to return is the persons , the companies and all the relationships between them  
Thanks

---

<div class="post-metadata">

**Author:** ![v2belleville](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/v2belleville/32/29050_2.png) [@v2belleville](https://community.neo4j.com/u/v2belleville)\
**Post date:** [August 16, 2023, 1:43pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/8 "2023-08-16T13:43:49Z")

</div>

```auto
MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company)
WITH p, size(collect(r)) as nbRel,collect(distinct type(r)) as rels,collect(distinct c.name) as cies
return p.name,rels,cies order by nbRel desc limit 10

```

or if you need the nodes

```auto

MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company)
WITH p, size(collect(r)) as nb Relorder by nbRel desc limit 10
MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company) return *

```

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [August 16, 2023, 2:52pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/9 "2023-08-16T14:52:40Z")

</div>

You should not have to match twice.

```auto
MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company)
WITH p, collect(c) as companies
ORDER BY size(companies) DESC
LIMIT 10
RETURN p as person, companies 

```

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [August 16, 2023, 2:56pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/10 "2023-08-16T14:56:09Z")

</div>

It’s a subquery expression. There are three: count, exists, collect.

> **[Expressions - Cypher Manual](https://neo4j.com/docs/cypher-manual/current/syntax/expressions/)**
>
> This section contains an overview of expressions in Cypher with examples.

---

<div class="post-metadata">

**Author:** ![v2belleville](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/v2belleville/32/29050_2.png) [@v2belleville](https://community.neo4j.com/u/v2belleville)\
**Post date:** [August 16, 2023, 5:30pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/11 "2023-08-16T17:30:57Z")

</div>

> [@glilienfield](#):
>
> You should not have to match twice.
> 
> ```auto
> MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company)
> WITH p, collect(c) as companies
> ORDER BY size(companies) DESC
> LIMIT 10
> RETURN p as person, companies 
> 
> ```

you do not return relationships

> What I need to return is the persons , the companies and all the relationships between them

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [August 16, 2023, 7:44pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/12 "2023-08-16T19:44:36Z")

</div>

oops, try this instead:

```auto
MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company)
WITH p, collect(c) as companies, collect(r) as relationships
ORDER BY size(companies) DESC
LIMIT 10
RETURN p as person, companies, relationships

```

or, if you want the relationships related to its company:

```auto
MATCH (p:Person)-[r:!ACCOUNTANT]-(c:Company)
WITH p, collect({company:c, relationship:r}) as companies
ORDER BY size(companies) DESC
LIMIT 10
RETURN p as person, companies

```

---

<div class="post-metadata">

**Author:** ![yanivts1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yanivts1/32/29440_2.png) [@yanivts1](https://community.neo4j.com/u/yanivts1)\
**Post date:** [August 23, 2023, 11:59am UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/13 "2023-08-23T11:59:24Z")

</div>

Thank, it's working...  
Now, i need to find connections between persons with 2-4 hops of specific relationships or any relationships...  
i tried to add this before "RETURN":  
MATCH (p)--\>(:Company)--(p:Person)  
and it doesn't work  
Thanks

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [August 23, 2023, 5:37pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/14 "2023-08-23T17:37:06Z")

</div>

I think your issue is that your match has 'p' related to 'p', which would be a loop. Don't put 'p' in the end node, as this constrains the node to be the same as the start node 'p', but with a further constraint of being a Person node.

If you want paths that are from 2 to 4 hops between node 'p' and another person, then try the following:

```auto
MATCH (p)-[*2..4]-(:Person)

```

If you provide the entire query and what is wrong, I can try to help further.

---

<div class="post-metadata">

**Author:** ![yanivts1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yanivts1/32/29440_2.png) [@yanivts1](https://community.neo4j.com/u/yanivts1)\
**Post date:** [August 24, 2023, 10:26am UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/15 "2023-08-24T10:26:09Z")

</div>

Thanks,

To be more specific:  
I want to find out if there is a connection between John and Jane based on relationships to companies with 2-4 hops...  
I tried this:  
MATCH (p {idNumber: 1111111})-[\*2..4]-(:Person{idNumber:33333333})RETURN \*  
Doesn't work

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [August 24, 2023, 1:07pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/16 "2023-08-24T13:07:42Z")

</div>

If you are only interested in the existence of the relationship you should use the ‘exits’ subquery expression. This was added in version 5.4 or 5.6 or there about. You can verify in the docs. It allows you to execute a complicated query and will return true or false corresponding to the query producing at least one record. If the query is a one line match, then you can use the exists function. It is not new.

The following is an example using the exists function since your query consists of a single match.

```auto
MATCH(p:Person{number:111111})
RETURN p, exists( (p)-[*2..4]-(:Person{idNumber:33333333}) ) as exists

```

---

<div class="post-metadata">

**Author:** ![yanivts1](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yanivts1/32/29440_2.png) [@yanivts1](https://community.neo4j.com/u/yanivts1)\
**Post date:** [September 14, 2023, 12:11pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/17 "2023-09-14T12:11:14Z")

</div>

Thanks,

But i need the 2 persons and the relationships between them, so i can see how they connected to each other

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [September 14, 2023, 12:45pm UTC](https://community.neo4j.com/t/retrieve-nodes-based-on-relationships-count-with-an-exception/63756/18 "2023-09-14T12:45:00Z")

</div>

Try this

```auto
MATCH(p:Person{idNumber:111111})
MATCH(m:Person{idNumber:33333333})
MATCH path=(p)-[*2..4]-(m)
RETURN p, m, relationships(path)

```

I am kinda lost on where we stand with your original query.