Well, I've seen your post, and here is the solution...
With this table definition:
mysql> DESC errors;
+-----------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------+------+-----+---------+-------+
| TramId | int(3) unsigned | | | 0 | |
| ErrorCode | char(1) | YES | | NULL | |
| Date | datetime | YES | | NULL | |
+-----------+-----------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
And this data inside:
mysql> SELECT * FROM errors;
+--------+-----------+---------------------+
| TramId | ErrorCode | Date |
+--------+-----------+---------------------+
| 100 | A | 2005-09-29 23:51:10 |
| 200 | B | 2005-09-29 23:51:24 |
| 201 | A | 2005-09-29 23:51:32 |
| 201 | A | 2005-09-29 23:51:41 |
| 201 | B | 2005-09-29 23:51:47 |
+--------+-----------+---------------------+
5 rows in set (0.00 sec)
We can use this query, which is very similar to yours...
SELECT
e1.ErrorCode AS Error1,
e1.Date AS Date1,
e2.ErrorCode AS Error2,
e2.Date AS Date2
FROM
errors e1, errors e2
WHERE
e1.Date <= e2.Date AND
e1.Date > date_sub(e2.Date, INTERVAL 10 SECOND) AND
e1.ErrorCode='A' AND
e2.ErrorCode='B';
And you'll get this answer:
+--------+---------------------+--------+---------------------+
| Error1 | Date1 | Error2 | Date2 |
+--------+---------------------+--------+---------------------+
| A | 2005-09-29 23:51:41 | B | 2005-09-29 23:51:47 |
+--------+---------------------+--------+---------------------+
And that's all.
Regards,
BVis.