Обсуждение: help with sql query

Hello,

Lets suppose I have a table like this one

id    id_1    id_2   date_time
1    101    1000    2006-07-04 11:25:43
2    102    1001    2006-07-04 11:26:43
3    101    1005    2006-07-04 11:27:43
4    103    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 ?

Thanks in advance for your help.

Peter

> 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