SQL statement for selecting information from 'next' row, last record drops off

Question

I'm having trouble with a select statement retrieving information from the 'next' row. I have a table in MySQL with state information (on or off), example records as below:

state table: (id is autoincrement)

+-----+----------+----------+---------------------+
| id  | asset_id | state_id | created             |
+-----+----------+----------+---------------------+
| 917 |       97 |        1 | 2013-10-13 22:07:03 |
| 918 |       97 |        0 | 2013-10-13 22:09:34 |
| 919 |       97 |        1 | 2013-10-13 22:09:52 |
| 924 |       97 |        0 | 2013-10-14 10:39:24 |
| 939 |       97 |        1 | 2013-10-14 12:47:26 |
| 940 |       97 |        0 | 2013-10-14 12:47:27 |
+-----+----------+----------+---------------------+

What I need to do is to come up with a statement that gives me state transition start and end times for each asset within a given date like this: The endtime is always the starttime of the next state. If the last state in a day stays on until the next day, I will report that as the endtime:

+----------+---------------------+---------------------+
| state_id | starttime           | endtime             |
+----------+---------------------+---------------------+
|        1 | 2013-10-13 22:07:03 | 2013-10-13 22:09:34 |
|        0 | 2013-10-13 22:09:34 | 2013-10-13 22:09:52 |
|        1 | 2013-10-13 22:09:52 | 2013-10-14 10:39:24 |
+----------+---------------------+---------------------+

Here's my current statement:

SELECT t1.state_id, t1.created as starttime, t2.created as endtime 
FROM state t1, state t2 
WHERE t1.created like "2013-10-13%" and t1.asset_id=97 and t2.created = 
(SELECT min(created) FROM state 
    WHERE id > t1.id and asset_id = 97 order by created desc);

This works fine when the date requested is not the last day of the resultset like in the example for '2013-10-13'. The problem is the last day and the last state (last record). if I run the example data for 2013-10-14, I get only two state transitions instead of three:

+----------+---------------------+---------------------+
| state_id | starttime           | endtime             |
+----------+---------------------+---------------------+
|        0 | 2013-10-14 10:39:24 | 2013-10-14 12:47:26 |
|        1 | 2013-10-14 12:47:26 | 2013-10-14 12:47:27 |
+----------+---------------------+---------------------+

The last one gets dropped of presumably because the subquery's where clause 'id > t1.id'. The record created '2013-10-14 12:47:27' is the last one in the table and doesn't have another record after it to fetch the endtime from.

My question is: Is it possible to change the query so that it also selects the last transition for example with a NULL as endtime like this:

+----------+---------------------+---------------------+
| state_id | starttime           | endtime             |
+----------+---------------------+---------------------+
|        0 | 2013-10-14 10:39:24 | 2013-10-14 12:47:26 |
|        1 | 2013-10-14 12:47:26 | 2013-10-14 12:47:27 |
|        0 | 2013-10-14 12:47:27 | NULL                |
+----------+---------------------+---------------------+

Answer 1

You need a LEFT JOIN. Try this. You can see it here: http://sqlfiddle.com/#!2/c9cb7/2

SELECT t1.state_id, t1.created as starttime, t2.created as endtime 
FROM state t1
LEFT JOIN state t2 
  ON t1.asset_id = t2.asset_id
   AND t1.created < t2.created
WHERE t1.created like "2013-10-13%" and t1.asset_id=97 
  AND NOT EXISTS
    (SELECT *
     FROM state
     WHERE state.created > t1.created
      AND state.created < t2.created)
ORDER BY starttime