MySQL Query: Query with conditional statements

Question

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

Jeremy Stein · Accepted Answer · 2009-12-09 14:37:15Z

1

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.

answered Dec 9, 2009 at 14:37

Jeremy Stein

19.8k18 gold badges71 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

noonex · Accepted Answer · 2009-12-09 14:52:10Z

1

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.

answered Dec 9, 2009 at 14:52

noonex

2,1231 gold badge19 silver badges20 bronze badges

Collectives™ on Stack Overflow

MySQL Query: Query with conditional statements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related