I'm doing an exercise and the first question is :

1- select the list of name with the mgr, in case the mgr is null show me 'President'

Now I'm quite new but how can I select all the column with a condition?

CodePudding user response：

You have not provided any information about column names, so I am using made up column names.

This is the sample table. There are two columns in it.

EMP | MGR
----------
ABC | DEF
DEF | NULL
XYZ | HJK
HJK | NULL
OPQ | DEF

Based on your requirement, I believe that you are trying to write President instead of NULL. So, this query might be working for you (This might need some changes based on your Database)

SELECT EMP,
       CASE
        WHEN MGR IS NOT NULL THEN MGR
        ELSE 'PRESIDENT'
       END AS 'MGR'
FROM MYTABLE;

Result should be like this:

EMP | MGR
----------
ABC | DEF
DEF | PRESIDENT
XYZ | HJK
HJK | PRESIDENT
OPQ | DEF

CodePudding user response：

This would be done with a case expression, IF(), IIF(), IFNULL(), ISNULL(), NVL(), or a COALESCE() function depending on what you've learned so far and which RDBMS you are using in class.

• Nearly every RDBMS besides MS Access: CASE WHEN mgr IS NULL THEN 'President' ELSE mgr END

• Nearly every RDBMS besides MS Access: COALESCE(mgr, 'President');

• Mysql and MariaDB: IFNULL(mgr, 'President')

• MS SQL Server and MS Access: ISNULL(mgr, 'President')

• Oracle: NVL(mgr, 'President')

• Mysql and Mariadb: IF(mgr IS NULL, 'President', mgr)

• MS Access: IIF(mgr is NULL, 'President', mgr)