CODEWITHSUNDEEP
sqlmany-to-many
E88
E88

Reputation: 107

SQL Select to display one to one, one to many from many to many relation

I'm trying to figure out SQL query that will list only one to one relation from below table.

Below Table contain 10 record where 8 are many to many relation data and 2 are one to one relation. Request your help with SQL that I can use to query below table and list out 2 records that has one to one relation. Note: Table support many to many relation.

Table1:

Field1  Field2  
1       a   
2       a   
3       b   
4       b   
5       c       One to One
4       d
6       d   
6       e   
7       f   
7       j   
8       g       One to One

enter image description here

Upvotes: 1

Views: 1075

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use window functions:

select t.*
from (select t.*, count(*) over (partition by field1) as cnt1,
             count(*) over (partition by field2) as cnt2
      from t
     ) t
where cnt1 = 1 and cnt2 = 1;

You can also use not exists:

select t.*
from t
where not exists (select 1 
                  from t t2
                  where t2.field1 = t.field1 and t2.field2 <> t.field2
                 ) and      
      not exists (select 1 
                  from t t2
                  where t2.field2 = t.field2 and t2.field1 <> t.field1
                 ) ;

Upvotes: 1

Related Questions