CODEWITHSUNDEEP
mysql
Adrian Preuss
Adrian Preuss

Reputation: 3113

Select username from other table, if NULL

How to select the row entry from an users table, if the other table has an NULL value?

SELECT
    CONCAT(`fh_users`.`username`, '_', `fh_ftp_user`.`username`) AS `userid`,
    `fh_ftp_user`.`password` AS `passwd`,
    '1010' AS `uid`,
    '1010' AS `gid`,
    `fh_ftp_user`.`path` AS `homedir`,
    '/sbin/nologin' AS `shell`
FROM
    `fh_ftp_user`,
    `fh_users`
WHERE
    `fh_ftp_user`.`username`='%U'
    OR
    (`fh_ftp_user`.`user_id`=`fh_users`.`id` AND `fh_users`.`username`='%U' AND `fh_ftp_user`.`username` IS NULL)"

Implicit, the table fh_ftp_user will be selected. fh_users will only used to CONCAT the username with the main username.

User Table

enter image description here

FTP Users

enter image description here

Result

enter image description here

Expected result

If fh_ftp_user.username is NULL, set fh_users.username as userid otherwise, if fh_ftp_user.username is not NULL, concat both usernames with an underscore as userid.

How i can create here an sub-query to handle these behavior?

Upvotes: 1

Views: 536

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

Using CASE you can handle the situation:

 SELECT
     CASE WHEN fh_ftp_user.username IS NULL THEN fh_users.username
          ELSE CONCAT(`fh_users`.`username`, '_', `fh_ftp_user`.`username`) 
          END AS `userid`,
     ....

Upvotes: 1

Related Questions