Matching a set of 4 numbers with another set of 4 numbers 1

[VickyC]

hello to all - I am stuck with one part of a much larger problem. Consider the table to the left...

For each combination of ID_Test and ID_Reference, there are 4 distinct values of X and 4 distinct values of X_Image. My problem is to find all cases where the 4 X values MAP ONTO the 4 X_Image values. (for example, [1,4,6,8] maps onto [6,8,1,4], but not onto [6,8,3,4]. Values can map onto themselves, so [1,4,6,8] also maps onto [8,4,6,1])

The output would look like the table to the Right.

 [bold]

ID_A    ID_B    X    X_Image                                          ID_A    ID_B  [/bold]
100      1      1       6                                             100      1
100      1      4       8                                             100      3
100      1      6       1                                             101      3
100      1      8       4

100      2      1       8 
100      2      4       2 
100      2      6       6
100      2      8       1

100      3      1       8 
100      3      4       4 
100      3      6       6
100      3      8       1

101      1      2       3 
101      1      4       8 
101      1      5       7
101      1      6       1

101      2      2       4 
101      2      4       2 
101      2      5       7
101      2      6       6

101      3      2       5 
101      3      4       4 
101      3      5       2
101      3      6       6

I would appreciate any help with this problem. Thank you in advance!

 

[PHV]

What about this ?

SELECT A.ID_A,A.ID_B
FROM tblVickyC A INNER JOIN tblVickyC B
ON A.ID_A=B.ID_A AND A.ID_B=B.ID_B AND A.X=B.X_Image
GROUP BY A.ID_A,A.ID_B
HAVING Count(*)=4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

hi PHV - that nailed it! Thank you
Vicky C.