MySQL query return column names in comma separated list where value is null

user_892134 0
Viewed 109

I want to return for each row, id and a comma separated list of column names that value is null/empty.

e.g. table name applicants

id  |  name  |  age  |  location
1   |  Matt  |  null |   london
2   |  Luis  |   24  |   paris 
3   |  null  |  null |   germany
4   |  James |   28  |   null

So the query results would look like:

id| missing
1 | age
3 | name,age
4 | location

I know how to return the rows but not the column names in a comma separated list/.

SELECT * FROM `applicants` WHERE name='' OR age='' OR location=''

How do i solve?

coalesce sql-null concatenation mysql
2 Answers

Use CASE expressions to check for nulls and CONCAT_WS to concatenate the results:

SELECT id,
       CONCAT_WS(
         ',',
         CASE WHEN name IS NULL THEN 'name' END,
         CASE WHEN age IS NULL THEN 'age' END,
         CASE WHEN location IS NULL THEN 'location' END
       ) missing  
FROM applicants
WHERE name IS NULL OR age IS NULL OR location IS NULL 
ORDER BY id;

See the demo.

user_104988280

The following should work for you:

SELECT  a.id,
        CONCAT_WS(',',
                  CASE WHEN a.name IS NULL THEN 'name' END,
                  CASE WHEN a.age IS NULL THEN 'age' END,
                  CASE WHEN a.location IS NULL THEN 'location' END) AS missing
FROM   applicants AS a
WHERE  a.name IS NULL 
OR     a.age IS NULL
OR     a.location IS NULL;

Example on DB Fiddle

user_10484250
Related