help with sql query

id id_1 id_2 date_time
1 101 1000 2006-07-04 11:25:43
I want to find all records have same id_1, but different id_2 and have
difference in time less than 5 minutes.
In this case this is record 1 and record 3.
How can I do this ?

I am sure that this will need some "tuning" to get it to work correctly but should give you one
way to get what you want.

select a.id, a.id_1, a.id_2, a.date_time

from table1 a join table1 b on (a.id_1 = b.id_1)
where
a.id_2 != b.id_2
and
abs( a.date_time - b.date_time) < ' 5 minutes'
;

Richard Broersma Jr wrote:

id id_1 id_2 date_time
1 101 1000 2006-07-04 11:25:43
I want to find all records have same id_1, but different id_2 and

have

difference in time less than 5 minutes.
In this case this is record 1 and record 3.
How can I do this ?

I am sure that this will need some "tuning" to get it to work
correctly but should give you one
way to get what you want.

select a.id, a.id_1, a.id_2, a.date_time

from table1 a join table1 b on (a.id_1 = b.id_1)
where
a.id_2 != b.id_2
and
abs( a.date_time - b.date_time) < ' 5 minutes'
;

... maybe use 'a.id_2 < b.id_2' if you want to exclude double results.

Yours,
Laurenz Albe