Extracting event order from SQL table

I have a SQL table similar to this:

+----+-------+--------+-------+-------+
| rd | ts_in | ts_out | sens  | devid |
+----+-------+--------+-------+-------+
|  1 |     0 |   1577 |     2 |    2  |
|  1 |   229 |   1549 |     1 |    2  |
|  1 |   123 |   1322 |     0 |    2  |
+----+-------+--------+-------+-------+

Where a data logger ends up storing it's data.

This data logger is stored underground and it have two inductive devices that detect when a car walks on top of it, one on it's beginning, another on it's end. It also have a device between the two sensors which weights the car axis on top of it.

When an inductive device detects a car is on top of it, the datalogger takes a timestamp of it's internal clock (ts_in) and waits until the sensor is deasserted, by then it takes another snapshot (ts_out) and it inserts a line on the table containing the datalogger id (devid), the sensor which triggered (1 = front, 2 = rear) the event, and the related timestamps. It also sets the rd field to a per-sensor unique counter (but it's not a per-device, as you can notice in the example).

The weight device also have similar behavior but it stores sens=0 and other data not present in the example (but it's ts_in and ts_out works the same).

One can think of I would always get data like this: sens 1 -> sens 0 -> sens 2 if the vehicle goes in some direction and: sens 2 -> sens 0 -> sens 1 if the vehicle goes in the opposite direction, but there are caveats:

a) the sensors may fail. The weight doesn't, so sometimes I can get only a sens 0 event, other times it gets it and one of the sensors but not the other.

b) I need to match the events around a sens0 entry: if I have a sens0 entry with a ts_out > sens1_tsout && sens2_tsout, I know those sens1/sens2 entries can't belong to this one.

In order for this information to be useful to me, I need to do a self join on the table and extract the following information:

• I need do determine, for every sens0 entries, if there's a pair of matching sens1 & 2 entries for which it's ts_out < sens0_tsout, and I also need to get the ts_in of the first sensor that hits (something in mysql like LEAST(a.ts_in, a.ts_out) AND GREATEST(a.ts_out, b.ts_out).

What I expect in output is something like (the column order is unimportant):

+------| --------- | ------------+
|devid | direction |  sens0_data |
+------| --------- | ------------+
| 2    |  *text*   |  *weigth*   |
+------| --------- | ------------+

Where text could be something like:

"forward" (i.e. tsin_sens11 > tsin_sens2 && tsout_sens2 > tsout_sens1);

"backward" (i.e.tsin_sens12 > tsin_sens1 && tsout_sens1 > tsout_sens2) "forward_rewind" (i.e. tsin_sens1 > tsin_sens2 && tsout_sens2 < tsout_sens1);

"backward_rewind" (i.e. tsin_sens2 > tsin_sens1 && tsout_sens1 < tsout_sens2);

NULL (i.e. there's no lines maching the above conditions, inclusive the case where there's no matching sens1/2 lines where ts_out < sens0 ts_out).

Basically, I need to prove, for every sens0 line, that I either:

• Have a nearby (in terms of ts_in & ts_out) pair of sens = 1 and sens = 2 lines;

• Having those, that the car entered on one direction (which I must differentiate) and left on the other, or that it went backwards.

• That, absent those conditions, I only have the sens = 0 data and NULL for the direction field.

Is that feasible in two or three inner joins or should I take a multiple query step?