CODEWITHSUNDEEP
mysql
Treby
Treby

Reputation: 1320

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 ON 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!!!

Upvotes: 1

Views: 376

Answers (2)

noonex
noonex

Reputation: 2075

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.

Upvotes: 1

Jeremy Stein
Jeremy Stein

Reputation: 19661

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.

Upvotes: 1

Related Questions