matching triplets of numbers in one column with another column 2

[teach314]

i have TableA like this below. . .

TableA                             QueryA
ID   Code     X    Y               CodeX    CodeY
1    H3      23    78                Q1       H7
2    H3      81    19                H3       Q1 
3    H3     118     4 
             
4    H7      42    26
5    H7     108     1
6    H7      99   125 

7    L2      18    21
8    L2       9    38
9    L2      34    88 

10   Q1      26    23
11   Q1       1    81
12   Q1     125   118
...continues

I need to write QueryA that identifies matching triplets in column X and column Y.
the triplet (26,1,25) in column x has a match in column Y, so QueryA should have row Q1, H7.

also (23, 81, 118) has a match, so QueryA has another record, H3 Q1.

Help writing this query is requested please,
Thank you

 

[SkipVought]

hi,

Maybe...

SELECT a.Code, a.X, b.Y

FROM
  TableA a
, TableA b

WHERE a.Code = b.Code

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?

SELECT X.CodeX,Y.CodeY
FROM (SELECT G.CodeX,G.X1,A.X AS X2,G.X3
FROM TableA A INNER JOIN (
SELECT Code AS CodeX,Min(X) AS X1,Avg(ID) AS IDx2,MAX(X) AS X3 FROM TableA GROUP BY Code
) G ON A.ID=G.IDx2
) X INNER JOIN (SELECT G.CodeY,G.Y1,A.Y AS Y2,G.Y3
FROM TableA A INNER JOIN (
SELECT Code AS CodeY,Min(Y) AS Y1,Avg(ID) AS IDy2,MAX(Y) AS Y3 FROM TableA GROUP BY Code
) G ON A.ID=G.IDy2
) Y ON X.X1=Y.Y1 AND X.X2=Y.Y2 AND X.X3=Y.Y3

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

 

[kjv1611]

PHV, I just had to post here to say I thought that query is amazing. I built a new blank database just to see it work. That covers the whole scenario without a hitch!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

One question for PHV on this one - and I should probably know this already: Why use the Avg function on the ID?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[teach314]

hello PHV Awesome!