Unique records based on same IDs with different type
ID    Email

100  a@a.com
200  b@b.com

ID   Assigned ID  Role
   100    200        Normal user
   100    100        Super user
   200    200        Normal user

select t1.ID,t1.email,t2.role from table t1, table t2 where t1.ID=t2.ID;

ID     Email      Role

100    a@a.com    Super  user
100    a@a.com    Normal user
200    b@b.com    Normal user

ID     Email      Role
100    a@a.com    Super  user
200    b@b.com    Normal user

select t1.ID,t1.email,t2.role from table t1, table t2
  where t1.ID=t2.ID group by t1.email having t2.role='Super user'
  and not exists (  select t1.ID,t1.email,t2.role from table t1, table t2
  where t1.ID=t2.ID group by t1.email having t2.role='Super user')
  UNION ALL
  select t1.ID,t1.email,t2.role from table t1, table t2
  where t1.ID=t2.ID group by t1.email having t2.role<>'Super user';

select t1.ID,t1.email,t2.role
from   table t1, table t2
where  t1.ID=t2.ID
  and  t2.ID = t2.Assigned_ID