CODEWITHSUNDEEP
mysqldatetimeself-join
Cafe
Cafe

Reputation: 61

Self-join to extract related records from event table?

I have a table that has the following fields

Time                 |     Status
2016-01-21 16:11:00  |     Connected
2016-01-21 16:51:00  |     Disconnected
2016-01-21 17:01:00  |     Connected
2016-01-21 17:10:00  |     Status X
2016-01-21 17:20:00  |     Status Y
2016-01-21 17:25:00  |     Disconnected
2016-01-21 17:30:00  |     Connected
2016-01-21 17:32:00  |     Disconnected

I want the output to be like this

Disconnected                     Connected
2016-01-21 16:51:00     |         2016-01-21 17:01:00
2016-01-21 17:25:00     |         2016-01-21 17:30:00
2016-01-21 17:32:00     |

and so on. That is, I want to show the data for the server disconnected times.

I've written a query like this 

select B.Time, A.Time
from ( select Time
       from table
       where Status = 'Connected') as A, table B
where B.Status = 'Disconnected'
and B.Time < A.Time;

My output is

Disconnected                     Connected
2016-01-21 16:51:00              2016-01-21 17:01:00
2016-01-21 16:51:00              2016-01-21 17:30:00

That is, for each disconnected event I'm getting all the connected events greater than it. However, I want only the first connected event greater than the disconnected event for each row. What can I do?

Upvotes: 1

Views: 58

Answers (3)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

You can group your output by disconnect time and use min to get the earliest:

SELECT B.Time,
       min(A.Time)
FROM
    (SELECT TIME
     FROM TABLE
     WHERE Status = 'Connected') AS A,
     TABLE B
WHERE B.Status = 'Disconnected'
    AND B.Time < A.Time
GROUP BY 1
;

or without subquery:

SELECT tab1.`time`,
       min(tab2.`time`)
FROM tab1 AS tab1
LEFT JOIN tab1 AS tab2 ON tab2.time > tab1.time
WHERE tab1.Status = 'Disconnected'
GROUP BY 1
;

Upvotes: 1

R.K123
R.K123

Reputation: 159

I have used this query before and it seems to work well

SELECT B.Time, A.Time
FROM table
WHERE NOT EXISTS (
    SELECT time
    WHERE b.Time = a.Time
    AND b.Disconnected > a.Connected
)

Upvotes: 0

O. Jones
O. Jones

Reputation: 108806

A good approach to this is to create a more complex ON clause that contains all the time logic. (http://sqlfiddle.com/#!9/81f61/5/0)

select dis.Time as disconnected, con.Time as connected
  from ev dis
  join ev con  ON con.Status = 'connected' 
              AND dis.Status = 'disconnected'
              AND con.Time = (select MIN(Time) 
                                 from ev
                                 where Status = 'connected'
                                   and Time > dis.Time)

This restricts each joined row to considering only the first MIN() connect time later than each disconnect time.

If you have a large number of these event records, consider placing an index on (Status, Time) to optimize this query.

Upvotes: 3

Related Questions