Martin AJ Martin AJ - 2 months ago 9
SQL Question

How can I set Unknown as column's value when there isn't any user's name?

Here is my query:

SELECT CONCAT(u.fname, ' ', u.lname) full_name
FROM posts p
LEFT JOIN users u
ON p.author_id = u.id AND p.visibility = 1


My query returns an user's full name (both his first and last name as a single string) where the value of
visibility
column equals
1
. But sometimes the value of
visibility
column isn't
1
, it is
0
. In that case, my query returns nothing. Well that's not what I want. I want either:
{user's full name}
or the string of
Unknown
.

So I need to check if
full_name
is empty, then set it the word of
Unknown
. I can do that by PHP after fetching. But I want to know how can I do that by pure MySQL?

Answer

Just use a CASE, and you probably will also need COALESCE for the nulls values.

SELECT CASE WHEN p.visibility = 1 
            THEN COALESCE(CONCAT(u.fname, ' ', u.lname), 'Unknown')
            ELSE 'Unknown'
        END as full_name
FROM posts p
LEFT JOIN users u 
ON p.author_id = u.id