Difficulties with SELECT query 1

[VickyC]

hello

ByteMizer and JonFer gave great advice that helped me with an SQL problem a few weeks ago. I now have a related problem that I've been struggling with for a week. I can get it to work, but VERY SLOWLY. In the example I describe, I show tblMain with 5 data columns (C1 to C5), but the actual tblMains I am working with have 10 data columns and hundreds of thousands of rows.

Consider two tables... [tt]

tblMain tblPairs
ID C1 C2 C3 C4 C5 inp outp
1 28 26 11 56 50 56 11
2 28 44 49 49 11 50 13
3 49 44 13 44 44 49 44
4 11 49 49 44 28 44 49
5 28 13 28 56 11 28 26
6 13 11 56 28 26 26 28
7 49 49 50 49 44 13 50
..... 11 56
800000 49 11 11 13 56

I need a query that will SELECT all rows in tblMain that follow this pattern...

In the tblMain shown, row 1 has values 28, 26, 11, 56, 50.
When REVERSED, we get... 50, 56, 11, 26, 28.
Replacing values from tblPairs we get 13, 11, 56, 28, 26
[/tt]
But this is just row 6 of tblMain, so I DON'T want to select it because it 'matches' row 1. Similarly, row 7 is not selected because it 'matches' the earlier row 3. In the tblMain shown, I would SELECT only rows 1, 2, 3, 4, 5, 800000.

I tried to do this by creating a VBA function that would look up values in tblPairs like.... F(56) = 11. Then I used SQL to INNER JOIN tblMain as M1 to tblMain as M2 ON M1.C1 = F(M2.C5) AND M1.C2 = F(M2.C4) AND ...etc. This work, but is exceedingly slow on my actual VERY LARGE table.

I'm really hoping there is a nice way to do this, hopefully just using SQL, that is fast!

many thanks
Vicky C.

 

[PHV]

I used SQL to INNER JOIN tblMain as M1 to tblMain as M2 ON M1.C1 = F(M2.C5) AND M1.C2 = F(M2.C4) AND ...etc
A starting point:

SELECT ...
FROM tblMain M1 INNER JOIN (
SELECT P1.outp AS O1,P2.outp AS O2,P3.outp AS O3,P4.outp AS O4,P5.outp AS O5
FROM ((((tblMain M
INNER JOIN tblPairs P1 ON M.C1=P1.inp)
INNER JOIN tblPairs P2 ON M.C2=P2.inp)
INNER JOIN tblPairs P3 ON M.C3=P3.inp)
INNER JOIN tblPairs P4 ON M.C4=P4.inp)
INNER JOIN tblPairs P5 ON M.C5=P5.inp
) M2 ON M1.C1=M2.O5 AND M1.C2=M2.O4 AND ...

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

 

[VickyC]

Thanks PHV!

The part of your code after "INNER JOIN (" and before ") M2" (see below) works really well. I changed the order of items in the SELECT.
[tt]

SELECT M.ID, P5.outp AS O1, P4.outp AS O2, P3.outp AS O3, P2.outp AS O4, P1.outp AS O5
FROM ((((tblMain M
INNER JOIN tblPairs AS P1 ON M.C1 = P1.inp)
INNER JOIN tblPairs AS P2 ON M.C2 = P2.inp)
INNER JOIN tblPairs AS P3 ON M.C3 = P3.inp)
INNER JOIN tblPairs AS P4 ON M.C4 = P4.inp)
INNER JOIN tblPairs AS P5 ON M.C5 = P5.inp
;
[/tt]

This produces the values in the table to the Right. The original tblMain is shown at the left.
[tt]

tblMain M2
ID C1 C2 C3 C4 C5 ID O1 O2 O3 O4 O5
1 28 26 11 56 50 1 13 11 56 28 26
2 28 44 49 49 11 2 56 44 44 49 26
3 49 44 13 44 44 3 49 49 50 49 44
4 11 49 49 44 28 4 26 49 44 44 56
5 28 13 28 56 11 5 56 11 26 50 26
6 13 11 56 28 26 6 28 26 11 56 50
7 49 49 50 49 44 7 49 44 13 44 44
[/tt]

So far so good. But I'm having difficulty with the 'outer' part of the SQL. The code finds that tblMain's ID = 1 matches ID = 6, according to the stated criteria, and ID = 3 matches ID = 7. So, the SQL selects rows 1, 3, 6, 7. I need the SQL to select all rows in tblMain except the those that match an earlier (lower ID) row. SO, all rows would be selected EXCEPT row 6 and row 7.

I'm getting severly tangled up trying to figure out this SQL. (here's a bit more info in case it is useful: If row X has a 'match' in row Y, then Row Y would 'match' Row X. I want to select only the 1st of these encountered. There may also be rows Z that DO NOT have a 'match' as per the stated criteria. These must also be selected. Only 'matches' of earlier rows are not selected.)

Thanks so much for any help
Vicky C.