CODEWITHSUNDEEP
mysql
user1037355
user1037355

Reputation:

MySQL IF returning nothing

I am trying to do some optimisation, currently post mysql work is done on the results to set a new paramter $class_subject... so i am trying get this already calculated in mysql...

SELECT
     class_grade.results                as results,
     subjects.subject                   as subject,
     subjects_pseudonyms.pseudonym      as pseudonym,
     IF( subjects_pseudonyms.pseudonym = null, subjects.subject, subjects_pseudonyms.pseudonym ) as class_subject
 FROM
     class_grade
 INNER JOIN class               ON class_grade.class_ID         = class.class_ID
 INNER JOIN subjects            ON class.subject_ID             = subjects.a_ID
 LEFT JOIN  subjects_pseudonyms ON class.subject_pseudonym_ID   = subjects_pseudonyms.a_ID
 WHERE
     class_grade.teacher_ID     = :teacher_id AND
     class_grade.class_ID       = :current_class_ID AND
     class_grade.report_set_ID  = :report_set_ID AND
     class_grade.student_ID     = :current_student_ID

In the above query the pseudonym might be null, if so I am attempting to set a new variable class_subject to be either subject or pseudonym...

The query runs fine, a results example is:

[results] => 71
[subject] => Law
[pseudonym] => 
[class_subject] =>

The problem is, the class_subject is not being populated..

Is there something wrong with my IF() cond?

Thanks, John

Upvotes: 1

Views: 38

Answers (1)

po_taka
po_taka

Reputation: 1856

You need to use IS NULL instead of = NULL or ISNULL() http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull

ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields false.)

Upvotes: 1

Related Questions