How can I count the number of unique pairs in a table that has a recursive relationship?

Question

Given the following table (people_table):

PersonId  CoupleId  
-------------------
   1        2  
   2        1  
   3        (null)  
   4        5  
   5        4  

By writing a query similar to this

Select count(?) from people_table

I expect 2 as count result

Answer 1

select count(*) from table
where personID in (select coupleID from table)
and coupleID in (select personid from table)
and coupleID > personID

hope it helps!

Answer 2

You can apply least() and greatest() functions to personid and coupleid
and group by them and finally count the number of rows:

select count(*) counter from (
  select least(personId, coupleId), greatest(personId, coupleId)  
  from people_table
  where personId is not null and coupleId is not null
  group by least(personId, coupleId), greatest(personId, coupleId)
)

See the demo

Answer 3

I think the simplest method is:

select count(*)
from people_table
where PersonId < CoupleId  ;

Why does this work? Well, two people in a couple have different ids. One of them must be smaller than the other and this counts one of them.

This also filters out NULL values.

Note: This assumes that your data is well-formed -- that is, both persons in a couple are in the table as separate rows.

Answer 4

below would be the query using joins -

 with table1 as ( 
select 1 as PersonId , 2 as CoupleId from dual
union 
select 2 as PersonId , 1  as CoupleId from dual
union
select 3 as PersonId , null as CoupleId from dual 
union 
select 4 as PersonId , 5 as CoupleId from dual
union
select 5 as PersonId , 4 as CoupleId from dual)
select count(*) from table1 t1 inner join table1 t2 on t1.personId=t2.coupleid and t1.coupleId=t2.personId
where t1.personId>t1.coupleId;