sql to identify matching triples 1

[teach314]

I need help with some sql code that looks for matches in two tables.

tbl1 tbl2

Id X Y Z Id X Y Z
---------------- -----------------
100 1 A7 45 300 1 G2 110
101 2 A7 432 301 2 G2 9
102 3 A7 8 302 3 G2 34

103 1 B4 32 303 1 B9 22
104 2 B4 11 304 2 B9 33
105 3 B4 162 305 3 B9 44

106 1 E3 133 306 1 C7 99
107 2 E3 444 307 2 C7 123
108 3 E3 18 308 3 C7 3

109 1 F2 110 309 1 D1 13
110 2 F2 9 310 2 D1 2
111 3 F2 34 311 3 D1 978

112 1 A1 2 312 1 C2 133
113 2 A1 61 313 2 C2 444
114 3 A1 62 314 3 C2 18
and so on and so on

in both tables, colums X repeats 1,2,3 1,2,3 ...
in both tables, column Y repeats triples like A7, A7, A7, B4, B4, B4 ...

I am trying to write a query (no luck yet) that shows all values of tbl1.Y and any values of tbl2.Y that have a matching triple of values in the same order in column Z. (for each Y in tbl1, there will be either NO matches, or 1 match in tbl2) For the table above, the output should look like this.

tbl1.Y tbl2.Y
---------------
A7
B4
E3 C2 because they both have Z values 133, 444, 18
F2 G2 because they both have Z values 110, 9, 34
A1

I'm hoping to get some help with this sql.

 

[PHV]

What about this ?

SELECT DISTINCT A.Y,B.Y2
FROM tbl1 A LEFT JOIN (
SELECT T1.Y AS Y1,T2.Y AS Y2 FROM tbl1 T1 INNER JOIN tbl2 T2 ON T1.X=T2.X AND T1.Z=T2.Z
GROUP BY T1.Y,T2.Y HAVING Count(*)=3
) B ON A.Y=B.Y1

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

 

[teach314]

that works! This code was a little beyond my coding ability, but I dissected all the parts and it now makes sense. There's a few other places I can use these ideas. thanks