CODEWITHSUNDEEP
phpmysql
Aditi
Aditi

Reputation: 69

If condition with is null in mysql

I am using 'if condition' in my query but its not working with ISNULL. Its given not value as well.

my query is

 SELECT *,IF(posted_job_id IS NULL,0,1)  as pj FROM appliedjob 


id|posted_job_id|pj
1 |             | 1
2 |  8          | 1
3 |  5          | 1
1 |             | 1
2 |  4          | 1

It showing 1 for all not null value as well. Its ok with case I mean when I am using case.

Upvotes: 0

Views: 422

Answers (3)

user2727841
user2727841

Reputation: 725

I've tested this query its working now.

SELECT posted_job_id, IF(posted_job_id > 0, posted_job_id, 0)
AS pj FROM appliedjob

MySQL IF() takes three expressions and if the first expression is true, not zero and not NULL, it returns the second expression. Otherwise it returns the third expression.

Suppose first experssion is if condition if its true, it return second experssion which is the body of if condition otherwise it will return third experssion which is just like else.

Upvotes: 2

PravinS
PravinS

Reputation: 2584

you can only check posted_job_id > 0 in SQL

SELECT *,IF(posted_job_id > 0, 1, 0)  as pj FROM appliedjob

Upvotes: 0

zzlalani
zzlalani

Reputation: 24354

If you want 0 in case of null and the original value in case of not null, try this

SELECT *, IF(posted_job_id IS NULL, 0, posted_job_id) AS pj 
FROM appliedjob

Upvotes: 0

Related Questions