MySQL Query: Query with conditional statements

i have this query

SELECT 
IF(isnull(ub.user_lecture_id), 0, ub.user_lecture_id) as  IsPurchased,
cs.title,cs.start_date, cs.start_time, cs.end_time 
FROM campus_schedule cs 
LEFT JOIN campus_bookinfo cb O开发者_运维技巧N cs.bookid=cb.idx_campus_bookinfo 
LEFT JOIN user_lectures ub ON ub.id_product = cs.idx_campus_schedule AND ub.id_customer = 11 
WHERE cs.idx_campus = 1 and cs.title like '%%' and cs.status=1

Which Shows: Click to view Output

Explanation: if (IsPurchased == 0) it is not yet bought my customer

My Question: if you look at the time of row with IsPurchased=1, the time range is conflicting with the time in IsPurchases=0. how can i compare and conclude that the time of the same date of the query is conflicting to the time and date of the other rows. results may be 1 or 0 in a "conflict" field name

Hope you got the point. Thanks for the help!!!

To compare times, you will find it easier to use DATETIME fields.

To check for "conflicting" rows, you'll probably need to have a subquery in the WHERE clause.

Subquery should work but will be inefficient in mysql. You should create temporary table and analyze it. Or do the same inline, like:

set @lastdate=0;
set @lasttime=0;

select IsPurchased, title, start_date, start_time, end_time, if(@lastdate = start_date, @lasttime < end_time, 1) as CONFLICT, @lastdate:=start_date, @lasttime:=start_time
from (your_query ORDER BY start_date, start_time, end_time) t ;

that is just an idea, it worked for me several times.