How to find column names of a table where column is not null-CodePudding

I have a MySQL table named users and it has n nullable columns and x rows.

I need to get the names of the columns if any of the rows have a value in that column.

Example:

users Table

-----------------------------------------------
| id | ref | col1 | col2 | col3 | col4 | col5 |
-----------------------------------------------
| 01 | 100 | null | null | null | null | null |
-----------------------------------------------
| 02 | 100 | qwer | null | null | null | null |
-----------------------------------------------
| 03 | 100 | qwer | plod | null | null | null |
-----------------------------------------------
| 04 | 100 | trye | qwer | plod | null | null |
-----------------------------------------------
| 05 | 101 | plod | trye | qwer | null | trye |
-----------------------------------------------

In this table column col4 doesn't have a value in any rows. So when getting the column names,I need to exclude that column name.

desired output:

id, ref, col1, col2, col3, col5

Some queries I have tried, but has syntax errors:

#syntax error
SHOW COLUMNS FROM `users` WHERE COLUMN is NOT null;

Another idea I have is that, find a row where there is minimum null columns. (So it will be row with id 5 in the above table.)

Currently I am fetching all rows and process using PHP to get the results. Looking for a direct MySQL query.

Any help will be appreciated.

CodePudding user response：

You can generate a query that you can later execute:

select concat("select id ",CASE count(col1)
                                WHEN 0 THEN ""
                                ELSE ", col1"
                            END, " from users")
from users

possible results:

select id , col1 from users

select id from users

for the second part, if 0s are ok:

 select concat("select id ",CASE count(col1)
                            WHEN (select LEAST(count(col1), 
                                            count(col2), 
                                            count(col3))
                                from users) THEN ", col1"
                            ELSE ""
                            END, 
                            CASE count(col2)
                                WHEN (select LEAST(count(col1), 
                                                count(col2), 
                                                count(col3))
                                    from users) THEN ", col2"
                                ELSE ""
                            END,
                            " from users")
from users

result:

select id , col3 from users

if you want to exclude 0s you can pass something like this to LEAST():

select (case count(col1) when  0 Then 9999999999 Else count(col1) End)
 from users

Sorry, I didn't realize that you are looking at rows before writing it. This would work for columns, but you might get an idea how rewrite it to rows, after starting to look at number of nulls:

select tmp.id, 
    x y as total
from (select id, 
        col1,
        col2,
        case when col1 is null then 0 else 1 end x,
        case when col2 is null then 0 else 1 end y
    from users) tmp
group by tmp.id

then at rows with least nulls:

with tmp2 as
(select tmp.id, 
    x y as total
from (select id, 
        col1,
        col2,
        case when col1 is null then 1 else 0 end x,
        case when col2 is null then 1 else 0 end y
    from users) tmp
group by tmp.id)
select * 
from tmp2
where total = (select min(total) from tmp2)

CodePudding user response：

;with cte as(

select 
  count( case when id   is null then null else 1 end) as id
, count( case when ref  is null then null else 1 end) as ref
, count( case when col1 is null then null else 1 end) as col1
, count( case when col2 is null then null else 1 end) as col2
, count( case when col3 is null then null else 1 end) as col3
, count( case when col4 is null then null else 1 end) as col4
, count( case when col5 is null then null else 1 end) as col5
from table_name
)     select 'id  ' from cte where id  = 0 
union select 'ref ' from cte where ref = 0 
union select 'col1' from cte where col1 =0
union select 'col2' from cte where col2 =0
union select 'col3' from cte where col3 =0
union select 'col4' from cte where col4 =0
union select 'col5' from cte where col5 =0

CodePudding user response：

Try something like this:

SELECT col_name FROM (
  SELECT 'col1' AS col_name, count(*) as amount FROM users WHERE col1 is not null
  UNION ALL
  SELECT 'col2' AS col_name, count(*) as amount FROM users WHERE col2 is not null
  UNION ALL
  SELECT 'col3' AS col_name, count(*) as amount FROM users WHERE col3 is not null
  UNION ALL
  SELECT 'col4' AS col_name, count(*) as amount FROM users WHERE col4 is not null
  UNION ALL
  SELECT 'col5' AS col_name, count(*) as amount FROM users WHERE col5 is not null
) colnames
WHERE colnames.amount > 0