from a table with symmetric relation select every pair only once 1

[mikrom]

I have a table with symmetric relation, e.g. 10 is friend with 123 and 123 is friend with 10
The table looks like this:

with tab(personnr, personnr1) as (
   values(10, 123),               
         (123,10),                
         (20,456),                
         (456,20)                 
)                                 
select * from tab                 

 ....+....1....+....2....+....3 
      PERSONNR       PERSONNR1  
            10             123  
           123              10  
            20             456  
           456              20  
 ********  End of data  ********

How do I select every pair only once ?
i.e.:

PERSONNR       PERSONNR1  
      10             123  
      20             456

 

[mikrom]

this seems to work:

with TAB(PERSONNR, PERSONNR1) as (
   values(10, 123),               
         (123,10),                
         (20,456),                
         (456,20)                 
)                                 
select t1.PERSONNR, t1.PERSONNR1  
from TAB t1, TAB t2               
where                             
  t1.PERSONNR = t2.PERSONNR1 and  
  t1.PERSONNR1 = t2.PERSONNR and  
  t1.PERSONNR < t1.PERSONNR1      

 ....+....1....+....2....+....3 
      PERSONNR       PERSONNR1  
            10             123  
            20             456  
 ********  End of data  ********

 

[mikrom]

But what when they forgot to save the reverse pair in the table ?
For example the table seems like this:

 ....+....1....+....2....+....3 
      PERSONNR       PERSONNR1  
            10             123  
           123              10  
            20             456  
           456              20  
            30             789  
 ********  End of data  ********

i.e. the pair (30, 789) has not reverse pair (789, 30)
in that case I need to get it in my select too:

 ....+....1....+....2....+....3 
      PERSONNR       PERSONNR1  
            10             123  
            20             456  
            30             789  
 ********  End of data  ********

... but this query (I posted above) does not work for this case:

select t1.PERSONNR, t1.PERSONNR1  
from TAB t1, TAB t2               
where                             
  t1.PERSONNR = t2.PERSONNR1 and  
  t1.PERSONNR1 = t2.PERSONNR and  
  t1.PERSONNR < t1.PERSONNR1

Have you any suggestions ?

 

[mikrom]

It seems that I could select the record without reverse record with

select t1.PERSONNR, t1.PERSONNR1                                   
from TAB t1                                                        
where t1.PERSONNR not in (                                         
  select t2.PERSONNR1 from TAB t2 where t2.PERSONNR1 = t1.PERSONNR)

so solving the puzzle seems to be

with TAB(PERSONNR, PERSONNR1) as (                                 
   values(10, 123),                                                
         (123,10),                                                 
         (20,456),                                                 
         (456,20),                                                 
         (30,789)                                                  
)                                                                  
select t1.PERSONNR, t1.PERSONNR1 from TAB t1, TAB t2               
where                                                              
  t1.PERSONNR = t2.PERSONNR1 and                                   
  t1.PERSONNR1 = t2.PERSONNR and                                   
  t1.PERSONNR < t1.PERSONNR1                                       
union all                                                          
select t1.PERSONNR, t1.PERSONNR1 from TAB t1                       
where t1.PERSONNR not in (                                         
  select t2.PERSONNR1 from TAB t2 where t2.PERSONNR1 = t1.PERSONNR)

 ....+....1....+....2....+....3     
      PERSONNR       PERSONNR1      
            10             123      
            20             456      
            30             789      
 ********  End of data  ********

Is there any better way ?

 

[fredericofonseca]

this will do the trick

with tab 
(PERSONNR
, PERSONNR1
)
as
(select *
       from (values(10, 123),
           (123, 10),
           (20, 456),
           (456, 20),
           (30, 789)) t (X, X2)
)
select distinct case
                when t2.PERSONNR is null
                    then t1.PERSONNR
                when t2.PERSONNR is not null
                    then case
                    when t2.PERSONNR < t1.PERSONNR
                        then t2.PERSONNR
                    else t1.PERSONNR
                    end
                end
              , case
                when t2.PERSONNR is null
                    then t1.PERSONNR1
                when t2.PERSONNR1 is not null
                    then case
                    when t2.PERSONNR1 > t1.PERSONNR1
                        then t2.PERSONNR1
                    else t1.PERSONNR1
                    end
                end


from tab t1
left outer join tab t2
    on t2.PERSONNR = t1.PERSONNR1
    and t2.PERSONNR1 = t1.PERSONNR

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mikrom]

Hi Frederico,
Thank you very much. It works well. The point with LEFT OUTER JOIN was an excellent idea.

But now, when I modify my first attempt above in that I use left outer join and add to the condition the alternative with IS NULL, then it seems to work too:

with tab (PERSONNR, PERSONNR1) as(                               
  values(10, 123), (123, 10),                                    
        (20, 456), (456, 20),                                    
        (30, 789),                                               
        (40, 666)                                                
)                                                                
select                                                           
  t1.PERSONNR, t1.PERSONNR1                                      
from tab t1 left outer join tab t2                               
    on t1.PERSONNR = t2.PERSONNR1 and t1.PERSONNR1 = t2.PERSONNR 
where                                                            
  (t1.PERSONNR = t2.PERSONNR1 and                                
   t1.PERSONNR1 = t2.PERSONNR and                                
   t1.PERSONNR < t1.PERSONNR1)                                   
  or                                                             
   t2.PERSONNR is NULL                                           

 ....+....1....+....2....+....3    
      PERSONNR       PERSONNR1     
            10             123     
            20             456     
            30             789     
            40             666     
 ********  End of data  ********