MySQL comparisons between multiple rows
- by Hurpe
I have a MySQL table with the following columns: id(int), date (timestamp), starttime(varchar), endtime(varchar), ...
I need to find time slots that are occupied by two or more rows. Here is an example table
id| date |starttime|endtime |
__|_____________________|_________|________|
1 | 2010-02-16 17:37:36 |14:35:00 |17:37:00|
2 | 2010-02-17 12:24:22 |12:13:00 |14:32:00|
3 | 2010-02-16 12:24:22 |15:00:00 |18:00:00|
Rows 1 and 3 collide, and need to be corrected by the user. I need a query to identify such colliding rows - something that would give me the ID of all rows in the collision.
When inserting data in the database I find collisions with this query:
SELECT ID FROM LEDGER
WHERE
DATE(DATE) = DATE('$timestamp')
AND (
STR_TO_DATE('$starttime','%H:%i:%s') BETWEEN
STR_TO_DATE(STARTTIME,'%H:%i:%s') AND STR_TO_DATE(ENDTIME,'%H:%i:%s') OR
STR_TO_DATE('$endtime','%H:%i:%s') BETWEEN
STR_TO_DATE(STARTTIME,'%H:%i:%s') AND STR_TO_DATE(ENDTIME,'%H:%i:%s')
) AND
FNAME = '$fname'";
Is there any way to accomplish this strictly using MySQL or do I have to use PHP to find the collisions?
