How to use JOIN to match the records in mySQL-CodePudding

I have a dataset as follows (below is a sample 7 records),

originating_date_time   number  operation
12/27/2022 16:26:39      11123    type1
12/27/2022 16:27:07      11232    type2
12/27/2022 16:27:11      11232    type3
12/27/2022 16:27:01      11245    type2
12/27/2022 16:27:13      11245    type3
12/27/2022 16:27:19      11239    type2
12/27/2022 16:27:39      11249    type3

This table contains the data with below conditions.

operation as type 1 (like first record) number may be available or null sometimes, I don't need to consider
operation as type 2 and type 3, and the time difference between those two records is less than 07 seconds for one particular msisdn (like second and third records). There may be one or more records (couples) for one particular number.
operation as type 2 and type 3, and the time difference between those two records is greater than 07 seconds for one particular msisdn (like fourth and fifth records). I don't need to consider
for one particular msisdn, type 2 record can be found but type 3 cannot found (like sixth record.) I don't need to consider
for one particular msisdn, type 3 record can be found but type 2 cannot found (like seventh record.) I don't need to consider

I only need to consider the condition mentioned in 2. only. And the final result should be as follows,

I tried as below, but this results null fields in the number field. Can someone show me how to do that?

select t1.originating_date_time, t1.number, t1.operation, 
       t2.originating_date_time, t2.number, t2.operation 
from tb1 t1 
join tb1 t2 
where t1.number=t2.number 
and t1.operation not like '%type1%'
and TIMEDIFF(t2.originating_date_time, t1.originating_date_time) = '00:00:07'

CodePudding user response：

One way, but <='07' seconds which matches your expected result but not the description on point 2.

with cte as (
              select originating_date_time,
                     number,
                     operation,
                     row_number() over(partition by number order by originating_date_time asc) rn
              from tb1
              where  operation in ('type2','type3') 
) select c1.originating_date_time,
         c1.number,
         c1.operation ,
         c2.originating_date_time,
         c2.number,
         c2.operation 
from cte c1
inner join cte c2 using(number)
where c1.rn=1 
and   c2.rn=2
and c1.operation = 'type2'  
and c2.operation = 'type3' 
and TIMESTAMPDIFF(SECOND , c1.originating_date_time, c2.originating_date_time) <='07' ;

https://dbfiddle.uk/BNdJhWLj