# Cypher query to retrieve common nodes and relationships

Hi ,

I have been trying to find out the cypher query to retrieve common nodes and relationships between two nodes and three nodes.

Please find below query which i have framed:
MATCH (d:cdr {name: '9455523455'})-[r]-(c:cdr)-[s]-(p:cdr {name: '9000000003'}) with c,r,s, collect(d) +collect(p)as inputs unwind inputs as input return DISTINCT input,c,count(distinct r),count(distinct s),collect(r),collect(s)

I require the relationship count s and r to be in same column.
Kindly help me

Hello,

Not sure what you mean by having the count in the same column?

You return count(distinct r) and count(distinct s) which return numbers as well as the first element of the collection collect(r). Are you saying that you want the type of the relationship as a column header?

That is not possible to do. The column headers must be specified and cannot be dynamic.

Elaine

Hi Elaine,

Below is the initial query which we are using currently in our application. Below query displays all connected nodes between given numbers which includes common nodes as well as individual nodes.

MATCH (m:cdr)-[r]-(n:cdr) WHERE n.name = "9455523455" OR n.name ="9000000003" RETURN m,n,count(r) as edge_count,collect(r) as r

M N Edge_count(count of relationship between m&n) Relationship r

9000000006 9455523455 1 CALLIN

9000000001 9455523455 1 CALLIN

9000000001 9000000003 3 CALLIN
CALLOUT
SMSIN

9000000027 9000000003 1 CALLOUT

With the above result we are showing single edge between two numbers with appropriate relationships count.Between 9455523455 and 9000000003 the common nodes are 9000000001 however,9000000006 are individually connected node to 9455523455 is also displayed.
So, i have managed to prepare the below query to obtain only common nodes for the inputs:9455523455 and 9000000003

MATCH (d:cdr {name: '9455523455'})-[r]-(c:cdr)-[s]-(p:cdr {name: '9000000003'}) with c,r,s, collect(d) +collect(p)as inputs unwind inputs as input return DISTINCT input,c,count(distinct r),count(distinct s),collect(r),collect(s)

The obtained result for my query is :

Input C count(distinct r) count(distinct s) Collect(r) Collect(s)|

9455523455 9000000001 1 3 CALLIN CALLIN
CALLOUT
SMSIN

9000000003 9000000001 1 3 CALLIN CALLIN
CALLOUT
SMSIN

C: common node
count(distinct r): Count between 9455523455 and common node
count(distinct s): Count between 9000000003 and common node

But expected output is as below:

M N Edge_count Relationship r

9455523455 9000000001 1 CALLIN|

9000000001 9000000003 3 CALLIN
CALLOUT
SMSIN|

Kindly help me.
Please let me know if further clarification is required.

Regards,
Janani