CODEWITHSUNDEEP
mysqlsqlconcatenationifnull
Joe Shmoe
Joe Shmoe

Reputation: 27

Query not returning anything when no last_name value found

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?

Upvotes: 0

Views: 63

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 1

Y.K.
Y.K.

Reputation: 692

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

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

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";

Upvotes: 1

Related Questions