×
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

Jobs

trouble with matching in a query

trouble with matching in a query

trouble with matching in a query

(OP)

Consider table tblA to the left. Each ID has rows 1 to 6. For each ID, the 6 values of
valx and valy form 3 matching pairs, each written both 'forwards' and 'backwards'.
For ID = 91, the three matching pairs are (24, 19), (18, 20), (21, 16).

I want to get output like the table tblB shown at right. This shows the row values
corresponding to the matched pairs. To avoid duplicates, I keep rowx < rowy.

CODE

tblA                                               tblB              

ID    row    valx    valy                           ID      rowx     rowy            
--------------------------                          ---------------------                
91     1      24      19                            91       1        3                                 
91     2      18      20                            91       2        6         
91     3      19      24                            91       4        5      
91     4      21      16            
91     5      16      21                            92  etc... 
91     6      20      18
                                   
92     1      21      16                         
92     2      20      18                    
... etc... 

Many thanks for any clues. My coding is a bit rusty, and I just can't get this to work.

Teach314

RE: trouble with matching in a query

Self-join the table, the sql below should be visible in design view too:

CODE -->

SELECT tblA.ID, tblA.row AS rowx, tblA_1.row AS rowy
FROM tblA INNER JOIN tblA AS tblA_1 ON (tblA.valx = tblA_1.valy) AND (tblA.ID = tblA_1.ID)
WHERE (((tblA.row)<[tblA_1].[row]))
ORDER BY tblA.ID, tblA.row; 

combo

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