Wanjia Wanjia - 1 year ago 116
MySQL Question

SQL IF value is found do ELSE do something else

Question

How can I get a simple table

userType = employee
or
userType = employer
if userID is found within the table employer.

Tried to do

With the following code I can get a return that sais this user is an employer when he is one, when he's not I get nothing.

SELECT (CASE WHEN (userID IS NOT NULL) THEN 'employer' ELSE 'employee' END) AS 'userType' FROM tblEmployer WHERE userID = 401


Also tried with and if statement

IF EXSISTS (SELECT userID FROM tblEmployer WHERE userID = 401)
IF ((SELECT COUNT(*) FROM tblEmployer WHERE userID = 401) > 0) ELSE ...


But this gives
Assignment type not recognized. (near "IF" at position 0)

Answer Source

If you want to return exactly one row, then use aggregation or a subquery in the select clause:

select (case when exists (select 1
                          from tblEmployer e
                          where e.userId = 401
                         )
             then 'employer' else 'employee'
        end) as userType

Alternatively:

select (case when count(*) > 0 then 'employer' else 'employee' end) as userType
from tblEmployer e
where e.userId = 401;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download