Making some SQL more efficient 1

[VickyC]

hi to all

I am looking for a way to make some SQL more efficient. The code I have works well, but I need to apply it 6 times to get the desired result. Here's the situation...

Tbl_1                                    Tbl_2
=====                                    =====

ID_ABC      A      B      C              ID_XY      X      Y
_____________________________            ______________________
   1       1       4      32               1        1      4
   2       1       4     806               2        1      6
   3       1       7      13               3        2     30                        
   4       1     423      93               4        7     12 
   5       2      45      30               5       30     45
 ...                                       ...

~12 million records                       ~38,000 records

I want to find all records in Tbl_1 where ANY PAIR from A, B, C DO NOT MATCH X and Y from Tbl_2.

In the example shown...
ID_ABC =1 and ID_ABC = 2 are rejected because 1 and 4 are found in ID_XY = 1
ID_ABC =5 is rejected because 45 and 30 are found in ID_XY = 5


The code I currently use looks like this...

INSERT INTO 
	Tbl_TEMPa
SELECT
	t1.ID_ABD, t1.A, t1.B, t1.C
FROM 
	Tbl_1 t1  LEFT JOIN  Tbl_2 t2  ON (t1.A = t2.X) AND (t1.B = t2.Y)
WHERE
	t2.X Is Null
ORDER BY
	t1.ID_ABC;

This works beautifully (and quickly, given the large table sizes), but it only 'finds' the cases where (A,B) <> (X,Y). In my actual tables, the code rejects ~280,000 records from Tbl_1 because they have matches with Tbl_2. I then take the output table, renamed as Tbl_1, and rerun the code to reject the cases where ...
(B,A) <> (X,Y),
(A,C) <> (X,Y),
(C,A) <> (X,Y),
(B,C) <> (X,Y), and
(C,B) <> (X,Y), always using the output table from each step as the input for the next step.


Is there a nice way to do this in 1 query?

thanks in advance for any clues
Vicky

 

[MajP]

I would think you could do a union query on the first and then do your left join. But not sure how that would handle the 12m records

Select
A as P1,
B as P2
From
tbl_1
Union
Select
B as P1,
A as P2,
From
tbl_1
Union Select
A as P1,
C as P2,
....
You then would have to do
A,B; B,A; A,C; C,A; B,C; C,B

So 1,4,32 would create all the pairs and then you can left join on table 2 and return the nulls.
1,4
4,1
1,32
32,1
4,32
32,4

So that would work, but that query returns 72million

 

[VickyC]

thanks MajP. The 72 million did cause a crash, but I can use your idea to use 2 runs instead of 6. Thanks!