×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

from a table with symmetric relation select every pair only once

thread178-1792521

from a table with symmetric relation select every pair only once

from a table with symmetric relation select every pair only once

(OP)
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:

CODE

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.:

CODE

PERSONNR       PERSONNR1  
      10             123  
      20             456

RE: from a table with symmetric relation select every pair only once

(OP)
this seems to work:

CODE

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  ********

RE: from a table with symmetric relation select every pair only once

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

CODE

....+....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:

CODE

....+....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:

CODE

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 ?

RE: from a table with symmetric relation select every pair only once

(OP)
It seems that I could select the record without reverse record with

CODE

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

CODE

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 ?

RE: from a table with symmetric relation select every pair only once

this will do the trick

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: from a table with symmetric relation select every pair only once

(OP)
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:

CODE

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  ********

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close