Help matching two sets on numbers 1

[teach314]

Greetings

I have a table that looks like this:

      [b][u]PK_ID        Set          X      Y[/u][/b]
 100          1           92     21
 101          1           84      6 
 102          1           71     93  
 103          1           21     84 
 104          1            6     66 

 105          2           55      9
 106          2           54     55
 107          2           26      6 
 108          2            9     54  
 109          2            6     26 

 110          3           92     88
 111          3           89     23
 112          3           88     19 
 113          3           23     92  
 114          3           19     89 

 115          4           68     73
 116          4           53     18
    ...   etc   ...

Set Column: Each Set value is repeated 5 times.
X Column: For each Set, there are always 5 integers in DESC order.
Y Column: For each Set, there are always 5 integers, but in mixed order.

Here is my question - I need to identify the Set values where X and Y represent the SAME set of 5 integers. In the example table above, Sets 2 and 3 pass this test, but Set 1 does not.


much thanks for any assistance

 

[Andrzejek]

Can you make them (X and Y) to match?

Something like:

Select ID, Set, X, [blue](
  Select Y
  From tblMyTable
  Where ID = T.ID
  and Set = T.Set
  and X = T.X
)[/blue] as Y
From tblMyTable T
Where Y is not NULL
Order By ID, X

That's just a 'wild stab' at it....

Have fun.

---- Andy

 

[PHV]

What about this ?

SELECT A.Set
FROM tblSet AS A INNER JOIN tblSet AS B ON A.Set = B.Set AND A.X = B.Y
GROUP BY A.Set
HAVING Count(*) = 5

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

 

[teach314]

Thanks to Andy and PH for taking the time to respond.