help with query, checking for double booking.

[exploo]

So. I'm making a booking system.
It's about booking of locals.
And I want to have a query to use, just to check if there is any double bookings.

This is how the table with all the booking look.
[pre]+---------------------+---------------------+-------+
| starttime          | endtime            | local |
+---------------------+---------------------+-------+
| 2006-11-05 11:00:00 | 2006-11-05 12:00:00 |    1 |
| 2006-11-12 11:00:00 | 2006-11-12 12:00:00 |    2 |
| 2006-11-19 11:00:00 | 2006-11-19 12:00:00 |    1 |
| 2006-11-22 11:00:00 | 2006-11-22 12:30:00 |    2 |
| 2006-11-22 11:00:00 | 2006-11-22 12:30:00 |    1 |
+---------------------+---------------------+-------+[/pre]

This is the query i use in my PHP function, when the data is insertet. to check if there's local is used between $startime2 and $endtime2.
[pre]SELECT 1 FROM bookings WHERE endtime>$startime2 AND starttime<$endtime2 AND local=$local;[/pre]
If a row is rerurned i know the local is busy and won't enter the data into the table.


But I also want a query for checking if a double booking somehow have happend, and is in the table.
In other words I would like to check the table against itself.

Can't really figure out how to do this.

I'm thankful for every help :)

[exploo]

I figured it out myself.

[pre]
SELECT s1.* FROM bookings s1, bookings s2  WHERE s1.endtime>s2.st
arttime AND s1.starttime<s2.endtime AND s1.id!=s2.id AND s1.local=s2.local;

Empty set (0.00 sec)
[/pre]

;D

[fenway]

FYI, you can use BETWEEN to make it a little clearer to read.

[exploo]

hm.. isn't BETWEEN used: A BETWEEN B AND C.
or is it a way to use it for my statement to?

cause i'm comparing endtime to $startime2 and then starttime to $endtime2.

[fenway]

Your'e right, my bad, I didn't read that closely enough.