SQL Query for events that happend in a specific order

Question

I have the following table:

+--------+-------+------+--+
| Object | Event | Time |  |
+--------+-------+------+--+
| Obj1   | A     |    1 |  |
| Obj1   | B     |    3 |  |
| Obj2   | A     |    7 |  |
| Obj2   | B     |    4 |  |
+--------+-------+------+--+

My goal is to get all objects that both had the event A & B with the condition that A happened first (in time). So far I only came up with the query to find me all objects that had A & B without including the time:

SELECT DISTINCT Object 
FROM
    (SELECT * 
     FROM
         (SELECT * 
          FROM table
          INNER JOIN 
              (SELECT Object Obj 
               FROM table 
               WHERE event LIKE '%A%' AS temp_table) ON table.Object = temp_table.Obj) AS temp_final 
     WHERE event LIKE '%B%') AS temp2;

So the end result would be that I get a table that includes only:

Obj1

Since this is the only Object that fulfills all criteria.

The time column is a Date stamp in real life, but for simplicity I used integers.

Thanks you for the help

Answer 1

If you are only tracking two events that happened one after the other, than you can solve this with a single JOIN.

This will work regardless of the number of events Obj1 has, as how you mentioned, you are only interested in A and B existing and being one after the other, respectively.

select distinct t1.object
from TABLE t1
    inner join TABLE t2 on t1.object = t2.object
        and t2.time > t1.time
        and t1.event = 'A'
        and t2.event = 'B'

Here is a sample of the result of the code:

declare @tbl table (obj varchar(10), event varchar(1), time int)

insert @tbl values ('Obj1', 'A', 1), ('Obj1', 'B', 3), ('Obj2', 'A', 7), ('Obj2', 'B', 4)

select distinct t1.obj
from @tbl t1
    inner join @tbl t2 on t1.obj = t2.obj
        and t2.time > t1.time
        and t1.event = 'A'
        and t2.event = 'B'