0

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.

Aditi
  • 69
  • 6

3 Answers3

2

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.

user2727841
  • 715
  • 6
  • 21
  • Thanks can u tell me why ISNULL not working ? while posted_job_id > 0 is working. – Aditi Jan 01 '14 at 07:26
  • MySQL IS NULL operator tests whether a value is NULL. If satisfied, then returns 1 otherwise returns 0. – user2727841 Jan 01 '14 at 07:31
  • In condition I checked posted_job_id is greater than 0 print database values like 8, 5 and 4 but if its not greater than 0 print 0. – user2727841 Jan 01 '14 at 07:40
0

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
zzlalani
  • 22,960
  • 16
  • 44
  • 73
0

you can only check posted_job_id > 0 in SQL

SELECT *,IF(posted_job_id > 0, 1, 0)  as pj FROM appliedjob
PravinS
  • 2,640
  • 3
  • 21
  • 25