If-else in mysql query where clause

Question

I am trying to change the where clause according to IF condition. The query fails for some reason. I would appreciate any help.

Thanks!

        SELECT
            c.id AS course,
            cr.id AS criteriaid,
            u.id AS userid,
            ue.timestart AS otimestart,
            (ue.timestart + cr.enrolperiod) AS ctimestart,
            ue.timecreated AS otimeenrolled,
            (ue.timecreated + cr.enrolperiod) AS ctimeenrolled
        FROM
            {user} u
        INNER JOIN
            {user_enrolments} ue
         ON ue.userid = u.id
        INNER JOIN
            {enrol} e
         ON e.id = ue.enrolid
        INNER JOIN
            {course} c
         ON c.id = e.courseid
        INNER JOIN
            {course_completion_criteria} cr
         ON c.id = cr.course
        LEFT JOIN
            {course_completion_crit_compl} cc
         ON cc.criteriaid = cr.id
        AND cc.userid = u.id
        WHERE
            cr.criteriatype = 5
        AND c.enablecompletion = 1
        AND cc.id IS NULL
        AND             
        (
        IF (ue.timeextension IS NULL)
                (ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?
          OR ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?)
        ELSE 
                (ue.timestart > 0 AND ue.timestart + ue.timextension < ?
          OR ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?)
        )

"Some reason" is almost always not good enough ;-) What's the exact error message or problem you have? — Olaf Dietsche
– Olaf Dietsche, Commented Oct 29, 2012 at 19:27

Barmar · Accepted Answer · 2012-10-29 19:37:32Z

2

The syntax of an IF expression is:

IF(<cond-expr>, <then-expr>, <else-expr>)

There's no ELSE keyword in the syntax, you just separate the two resulting expressions with a comma. So the correct syntax of your expression should be:

AND IF(ue.timeextension IS NULL,
       (ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?)
         OR (ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?),
       (ue.timestart > 0 AND ue.timestart + ue.timextension < ?)
         OR (ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?))

Another way you could write this is:

AND ((ue.timestart > 0 AND ue.timestart + IFNULL(ue.timeextesion, cr.enrolperiod) < ?)
     OR (ue.timecreated > 0 AND ue.timecreated + IFNULL(ue.timeextesion, cr.enrolperiod)) < ?)

answered Oct 29, 2012 at 19:37

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

Comments

Olaf Dietsche · Accepted Answer · 2012-10-29 19:35:57Z

0

Depending on what you want to accomplish, the correct where clause might be:

...
where ...
and (if(ue.timeextension IS NULL,
        ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?
            OR ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?,
        ue.timestart > 0 AND ue.timestart + ue.timextension < ?
            OR ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?);

answered Oct 29, 2012 at 19:35

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Comments

Yogendra Singh · Accepted Answer · 2012-10-29 19:38:55Z

0

I think you may try last section in the where i.e.

   AND             
    (
    IF (ue.timeextension IS NULL)
            (ue.timestart > 0 AND ue.timestart + cr.enrolperiod < ?
      OR ue.timecreated > 0 AND ue.timecreated + cr.enrolperiod < ?)
    ELSE 
            (ue.timestart > 0 AND ue.timestart + ue.timextension < ?
      OR ue.timecreated > 0 AND ue.timecreated + ue.timeextension < ?)
    )

as

   AND             
    (
      (ue.timestart > 0 AND ue.timestart + 
             IFNULL(ue.timeextension , cr.enrolperiod )  < ? )
     OR (ue.timecreated > 0 AND ue.timecreated + 
             IFNULL (ue.timeextension, cr.enrolperiod) < ? )
    )

answered Oct 29, 2012 at 19:38

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Collectives™ on Stack Overflow

If-else in mysql query where clause

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related