Re: Comparing two tables

[Natasha Hurley-Walker]

Hi Nick,

Thanks, I had a great weekend. And the Monday was all the better for your post; everything worked like a charm :) I have another element to add now, which may make the topic title slightly outdated:

I have another table of harmonics by which I want to multiply the Table2 Periods and compare the results to the Table 1 Periods. Table 3 has two columns, Harmonic and Multiplier, which are simply a varchar label and a 0 float =<3.

I can display all of the positive matches using:
SELECT Table1.Period, Table1.SNR, Table2.ID, Harmonics.Harmonic FROM Table1, Table2, Harmonics WHERE ABS(Table1.Period - (Table2.Period*Harmonics.Multiplier)) <= 0.01

But I would also like to display the full table without any of the matches. However I can't figure out where to put the Harmonics table when using the LEFT JOIN syntax. I have the feeling I need to create a temporary table like

------------------------------------------------------------------------------------------------------------------------
Table2.ID | Harmonics.Multiplier | Harmonics.Harmonic*Table2.Period
-------------------------------------------------------------------------------------------------------------------------

then compare the Table1.Period to the third column above, using the usual ABS syntax.

I'll be trying that for a bit; if there's an easier way let me know :)

Thanks a million,

Tash