CODEWITHSUNDEEP
mysql
George Moldovan
George Moldovan

Reputation: 83

IF operator in mysql issue

this is my query: 

SELECT d.name, d.department_id, d.email , if (d.status = 1, 'Active', 'Inactive') as status  dep.name as dep_name FROM doctors d INNER JOIN departments dep ON  dep.id = d.department_id

I want to select the status , if it is 1 then status will be "Active" else "Inactive". How can I do that?

At the moment when i execute my query, a message error appears:

> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
> near 'dep.name as dep_name FROM doctors d INNER JOIN departments dep
> ON dep.id = d.de' at line 1

Can you help me with this ? thx

Upvotes: 2

Views: 40

Answers (3)

Techy
Techy

Reputation: 2654

You can use CASE statement

case when <Condition>  then <value> else <value> end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You are missing a comma after the expression:

SELECT d.name, d.department_id, d.email,
       if(d.status = 1, 'Active', 'Inactive') as status,
-------------------------------------------------------^
       dep.name as dep_name
FROM doctors d INNER JOIN
     departments dep
     ON  dep.id = d.department_id;

Although I might prefer case because it is ANSI-standard, your problem is a missing comma.

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Though you have missed a , between status and dep_name

But In SQL you should use CASE:

SELECT d.name, d.department_id, d.email , 
CASE WHEN d.status = 1 
       THEN 'Active' 
       ELSE 'Inactive' 
 END  as status,
dep.name as dep_name FROM doctors d 
INNER JOIN departments dep ON  dep.id = d.department_id

Upvotes: 3

Related Questions