MySQL Return the first non-NULL value from the list of the column in select statement with column name

debugcn Published at Dev

user4676307

I have a sql query below:

SELECT 
    md.refereeInternetSearch,
    md.refereeCompanyColleague,
    md.refereeIndustryPeer,
    md.refereeIndustryEvent,
    md.refereeIndustryPublication,
    md.refereeMarketingEmail,
    md.refereeOther
FROM
    marketing_details md
WHERE
    md.id = 14588

From the 7 columns in the above select statement only one column will have a value and rest will be null. Is it possible to select just that one column value that is not null using some sort of sql statement ?

Shadow

Use the coalesce() function to return the 1st non-null value from a list of parameters:

SELECT 
    coalesce(md.refereeInternetSearch,
    md.refereeCompanyColleague,
    md.refereeIndustryPeer,
    md.refereeIndustryEvent,
    md.refereeIndustryPublication,
    md.refereeMarketingEmail,
    md.refereeOther) as non_null_value
FROM
    marketing_details md
WHERE
    md.id = 14588

However, it will not be able to tell you which column the value came from.

UPDATE

If you really want to use sql to retrieve the name of the field that has the non null value, then you can do that with the following monstrous sql statement below. What it does it concatenates each field value from a record into a single string, where the values are separated by comma. NULL values are converted to empty string. Then using find_in_set() function it finds the position of the only non null value within the above string. Then using the elt() function it returns the name of the field from the list of field name literals based on the position returned by find_in_set().

SELECT
    md.id, 
    coalesce(md.refereeInternetSearch,
    md.refereeCompanyColleague,
    md.refereeIndustryPeer,
    md.refereeIndustryEvent,
    md.refereeIndustryPublication,
    md.refereeMarketingEmail,
    md.refereeOther) as non_null_value,
    elt(find_in_set(coalesce(md.refereeInternetSearch,
                                 md.refereeCompanyColleague,
                                 md.refereeIndustryPeer,
                                 md.refereeIndustryEvent,
                                 md.refereeIndustryPublication,
                                 md.refereeMarketingEmail,
                                 md.refereeOther),
                        concat(coalesce(md.refereeInternetSearch,''),',',
                               coalesce(md.refereeCompanyColleague,''),',',
                               coalesce(md.refereeIndustryPeer,''),',',
                               coalesce(md.refereeIndustryEvent,''),',',
                               coalesce(md.refereeIndustryPublication,''),',',
                               coalesce(md.refereeMarketingEmail,''),',',
                               coalesce(md.refereeOther,'')
                              ) 
                       ),'refereeInternetSearch',
                         'refereeCompanyColleague',
                         'refereeIndustryPeer',
                         'refereeIndustryEvent',
                         'refereeIndustryPublication',
                         'refereeMarketingEmail',
                         'refereeOther'
      ) as field_name 
FROM
    marketing_details md
WHERE
    md.id = 14588

Huh, I hope I got all the parentheses right!

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-21

Comments

0 comments

From Dev

Related Related

Article

MySQL Return the first non-NULL value from the list of the column in select statement with column name

MySQL Return the first non-NULL value from the list of the column in select statement with column name

How do i return bit value from a select statement when a column value in the statement is null

First column name with non null value by row pandas

Select the first column name where a value is null by Id

Select the first column name where a value is null by Id

Laravel LeftJoin return duplicate value from first column (mysql)

Return the name of the MySQL column where a select match was

MySQL first select column based on parameter value and then value of column

MySQL first select column based on parameter value and then value of column

How to select value from second column if first column is blank/null in SQL (MS SQL)?

MySQL, select column name based on a value

Return the column name as as a value in a column

Return JSON from MySQL with Column Name

Return JSON from MySQL with Column Name

Return Value Column Name

Select column name and value from table

Invalid Column Name in Select Statement

Invalid Column Name in Select Statement

Get the column name for the first non-zero value in that row with pandas

MySQL - add text prefix before column name in SELECT statement

Data Table - Select Value of Column by Name From Another Column

For each row return the column index and name of non-NA value

Excel: return name of column with highest value in a non-continuous array

MySQL SELECT column FROM table WHERE column IS NULL

SQL query returning only values from first column in SELECT statement

MySQL return max value or null if one column has no value

Select COLUMN name from one table based on VALUE from another in mySQL?

Mysql select dynamic row values as column name from a table and value from another table

MySQL CodeIgniter Select or not from second table based on column of first Select

Return the first non-NULL value in the list, or NULL if there are no non-NULL values