Query not returning anything when no last_name value found

Question

I just built this new conditional query for pulling either a first_name AND last_name OR company_name based on the display_as value:

Select If(`display_as` = 'individual',
    CONCAT(first_name, ' ', last_name)
   ,`company_name`) as name FROM `{$this->table}` WHERE `unique_id` = ? LIMIT 1

The problem is, if the user has a first_name value only and no value for last_name, nothing is returned at all.

How can I fix this?

This happens because any operation involving a NULL and a NOT NULL value results to NULL, with the exception of IFNULL and COALESCE, which are used to address the issue by providing a default value in case of NULL. The answers given are typical examples of the functions usage. — Manos Anastasiadis, Aug 08 '19 at 06:39

score 1 · Accepted Answer · answered Aug 08 '19 at 06:10

1

use this query instead.

$sql = "Select If(`display_as` = 'individual',
    CONCAT(IFNULL(first_name, ''), ' ', IFNULL(last_name, ''))
   ,`company_name`) as name FROM `{$this->table}` WHERE `unique_id` = ? LIMIT 1";

answered Aug 08 '19 at 06:10

Ed Bangga

12,879
4
16
30

I would personally surround the CONCAT() expression with a TRIM() to remove trailing spaces in case of NULL. – Manos Anastasiadis Aug 08 '19 at 06:41

score 1 · Answer 2 · answered Aug 08 '19 at 06:10

1

try this one:

Select
    If( `display_as` = 'individual',
        CONCAT(coalesce(first_name, ''), ' ', coalesce(last_name, ''))
       ,`company_name`) as name
FROM `{$this->table}`
WHERE `unique_id` = ?
LIMIT 1

answered Aug 08 '19 at 06:10

Y.K.

682
4
10

score 1 · Answer 3 · answered Aug 08 '19 at 11:57

I would recommend writing this as:

select (case when display_as = 'individual' 
             then concat_ws(' ', first_name, last_name)
             else company_name
        end) as name 
from `{$this->table}` 
where unique_id = ?
limit 1;  -- probably not needed

Notes:

case is the standard SQL construct for conditional logic. if() is a bespoke MySQL extension.
concat_ws() elegantly handles NULL values in the names. It simply ignores the the value rather than returning NULL.
Backticks are not required everywhere. They just make the query harder to write and read.
If your unique_id is really unique, you don't need LIMIT 1.

Query not returning anything when no last_name value found

3 Answers3