gistfile1

mysql> SELECT
    ->   *
    -> FROM
    ->   (SELECT
    ->     pic.nid,
    ->     (
    ->       SELECT
    ->         pic2.sid
    ->       FROM
    ->         project_issue_comments pic2
    ->       WHERE
    ->         pic2.nid = 8 AND
    ->         pic2.comment_number = pic.comment_number - 1
    ->     ) as "original_state",
    ->     (
    ->       SELECT
    ->         pis2.name
    ->       FROM
    ->         project_issue_state pis2
    ->       WHERE
    ->         pis2.sid = original_state
    ->     ) as "original_state_name",
    ->     pic.sid as "new_state",
    ->     pis.name as "new_state_name",
    ->     pic.comment_number,
    ->     pic.timestamp
    ->   FROM
    ->     project_issue_comments pic
    ->   JOIN
    ->     project_issue_state pis ON pic.sid = pis.sid
    ->   WHERE
    ->     pic.nid = 8) as issuedata
    -> WHERE
    ->   issuedata.original_state <> issuedata.new_state;
+------+----------------+------------------------------------+-----------+------------------------------------+----------------+------------+
| nid  | original_state | original_state_name                | new_state | new_state_name                     | comment_number | timestamp  |
+------+----------------+------------------------------------+-----------+------------------------------------+----------------+------------+
|    8 |              1 | active                             |        13 | needs work                         |             26 | 1137231468 | 
|    8 |             13 | needs work                         |         8 | needs review                       |             42 | 1149795918 | 
|    8 |              8 | needs review                       |        13 | needs work                         |             43 | 1150067195 | 
|    8 |             13 | needs work                         |         8 | needs review                       |             47 | 1162485840 | 
|    8 |              8 | needs review                       |        14 | reviewed & tested by the community |             48 | 1162486884 | 
|    8 |             14 | reviewed & tested by the community |         4 | postponed                          |             50 | 1163275828 | 
|    8 |              4 | postponed                          |         8 | needs review                       |             51 | 1163454061 | 
|    8 |              8 | needs review                       |        13 | needs work                         |             54 | 1182648593 | 
|    8 |             13 | needs work                         |         8 | needs review                       |             58 | 1186659086 | 
|    8 |              8 | needs review                       |        13 | needs work                         |             59 | 1186664980 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            105 | 1230860889 | 
|    8 |              8 | needs review                       |        13 | needs work                         |            108 | 1230876700 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            109 | 1230877490 | 
|    8 |              8 | needs review                       |        13 | needs work                         |            110 | 1230877681 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            121 | 1230948038 | 
|    8 |              8 | needs review                       |        13 | needs work                         |            152 | 1231313326 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            153 | 1231313760 | 
|    8 |              8 | needs review                       |        13 | needs work                         |            155 | 1231325466 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            156 | 1231334541 | 
|    8 |              8 | needs review                       |        13 | needs work                         |            162 | 1231383612 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            163 | 1231391722 | 
|    8 |              8 | needs review                       |        13 | needs work                         |            164 | 1231397259 | 
|    8 |             13 | needs work                         |         8 | needs review                       |            165 | 1231399688 | 
|    8 |              8 | needs review                       |        14 | reviewed & tested by the community |            168 | 1231402247 | 
|    8 |             14 | reviewed & tested by the community |         2 | fixed                              |            171 | 1231404268 | 
|    8 |              2 | fixed                              |        13 | needs work                         |            172 | 1231404544 | 
|    8 |             13 | needs work                         |         2 | fixed                              |            174 | 1231408056 | 
|    8 |              2 | fixed                              |         7 | closed (fixed)                     |            177 | 1232704202 | 
+------+----------------+------------------------------------+-----------+------------------------------------+----------------+------------+
28 rows in set (0.33 sec)