Choosing related event sets in SQL

Question

So, now I'm working on a program that has a whole bunch of event objects. Each event object has a start and end time (both full timestamps). Many events are adjacent (one endTime event is the same as the next startTime event), indicating that two events occurred consecutively. If I had to store all my events in a SQLite or MySQL table, would it be possible to query each set of related events?

Say my table looks like this.

id   title          start_time           end_time
----------------------------------------------------------
1    Happy Time     4/3/2010 10:00:00    4/3/2010 13:00:00
2    Happier Time   4/3/2010 13:00:00    4/3/2010 18:00:00
3    Good Time      4/4/2010 18:00:00    4/4/2010 19:00:00
4    Gooder Time    4/4/2010 19:00:00    4/4/2010 22:00:00
5    Sad Time       4/6/2010 16:00:00    4/6/2010 20:00:00

I want to be able to query this table and see that ids 1 and 2 are adjacent, ids 3 and 4 are adjacent, and id 5 ... is in contact with itself.

I don’t know where to start, because whenever I worked with SQL queries, the results were returned as separate rows. One line as a result is never part of a subgroup.

If you have any questions, feel free to comment. Thank!

Answer 1

SELECT a.id, a.title, a.start_time, a.end_time, b.id, b.title, b.start_time, b.end_time
FROM TABLE1 a, TABLE1 b
WHERE a.start_time = b.end_time

EDIT: modified based on additional information:

SELECT a.id, a.title, a.start_time, a.end_time, b.id, b.title, b.start_time, b.end_time
FROM TABLE1 a left join TABLE1 b ON a.start_time = b.end_time