Hi all,

I have a problem cross-referencing two tables by time

My tables have TimeStamp-type columns but there are not exact matches
for instance, a record in table1 might look like:

20041111120000,data1,data2

and two records in Table2 might look like

20041111120002,data3,data4
20041111120010,data5,data5

i need a query that recognizes that:
a) the time difference between table1/record1 and table2/record1 is 2 seconds
b) the time difference between table1/record1 and table2/record2 is 10 seconds

and matches the data from the records with the smallest time difference
so result of query = data1,data2,data3,data4

thanks, hope that's clear

I assume that you know the time you're working with. Let's name that time $time
now we need two variables for +-10sec and two for +- 2sec
$maxtime2 = $time + 2
$mintime2 = $time - 2

$maxtime10 = $time + 10
$mintime10 = $time - 10

now we can query our db:

SELECT * from table1, table2 WHERE table1.time BETWEEN $maxtime2 AND $mintime2 AND table2.time BETWEEN $maxtime10 AND $mintime10

Hope this helps