If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

This data is wrong according to database implementation and is only inserted for producing the following query:
Basically i need to write a SQL query that will return the rooms that have overlaping booking times.
So the output must be 1 and 2 lines as their start and finish times overlap. Can anyone please tell me how this can be done?
Thanks in advance.

If you can change the start/end time fields to be TIME instead of CHAR (or even DATETIME or TIMESTAMP), and have the end_time be something like 18:59 (or 18:59.59) instead of 19:00, you could maybe do something like

Code:

select * from the_table as t1
inner join the_table as t2 on t1.room_code = t2.room_code
where
t1.start_time between t2.start_time and t2.end_time
or
t1.end_time between t2.start_time and t2.end_time

NotDog,
Thanks very much for your reply. I've already tried exactly the same code and some versions of it, but it does not work. The problem is that I am given already this scenario so I am not allowed to change the date type.

I suppose you could cast them to integer and subtract 1 from the end_time, perhaps? Oh, and I just realized you'd need to add a check in the where clause that the records are for the same date.

Code:

select * from the_table as t1
inner join the_table as t2 on t1.room_code = t2.room_code
where
t1.booking_date = t2.booking_date
and (
cast(t1.start_time as unsigned) between cast(t2.start_time as unsigned) and (cast(t2.end_time as unsigned) - 1)
or
(cast(t1.end_time as unsigned) - 1) between cast(t2.start_time as unsigned) and (cast(t2.end_time as unsigned) - 1)
)

However, the downside of this is that it will require scanning of all records where the booking dates match, since it will have to cast every start/end time, and thus cannot take advantage of any indexes on those fields, I believe.