SQL CROSS JOIN question

Question

I am having a bit of trouble with my SQL query.

I have two tables:

Table1

id  guid  title     D0      D1     D2
-----------------------------------------
1   guid1 Title1    0.123  -0.235  0.789
2   guid2 Title2   -0.343   0.435  0.459
3   guid3 Title3    0.243  -0.267 -0.934
...
100 guid4 Title100 -0.423   0.955  0.029

and Table 2 (note it has the same schema, just different data).

id  guid  title     D0      D1     D2
----------------------------------------
1   guid1 Title1    0.233  -0.436 -0.389
2   guid2 Title2   -0.343   0.235  0.789
3   guid3 Title3    0.573  -0.067 -0.124
...
100 guid4 Title100 -0.343   0.155  0.005

I am trying to figure out how to write a SELECT statement which returns all the titles WHERE all the combinations of ABS(Table1_D0*Table2_D0)+ABS(Table1_D1*Table2_D1)+ABS(Table1_D2*Table2_D2) are less than a thresholded value (hard coded probably).

So far I am trying to use a CROSS JOIN, but I am not sure if this is the correct approach.

Does this make sense? Table1, row1 against all the rows of Table2, then Table1, row2 against all the rows of Table2.

If it matters, I am using MS SQL.

Many thanks! Brett

Answer 1

SELECT t1.title
FROM Table1 t1
CROSS JOIN table2 t2
WHERE ABS(t1.D0*t2.D0)+ABS(t1.D1*t2.D1)+ABS(t1.D2*t2.D2)<10