0

I have a query that looks something like this:

select * 
from resident 
where Resident_Sex is null or Resident_Date_Of_Birth is null or race_code is null.

not always is it only checking if 3 columns are null, it may have more. I want to know if there is a way, instead of selecting * from this table, to select lets say the resident id and the column name that he is missing.

for example, it should return

resident_id column_name
----------- ------------
aaaaaaa     resident_sex
bbbbbbb     resident_sex
bbbbbbb     race_code   
ccccccc     resident_date_of_birth

instead of returning

 resident_id resident_sex race_code   Resident_date_of_birth
 ----------- ------------ ----------- -----------------------
 aaaaaaa     null         158         1995-02-18 00:00:00.000
 bbbbbbb     null         null        1928-07-15 00:00:00.000
 ccccccc     F            12          null

Obviously there are alot more rows and columns in the table so I cannot do any case or if statements...

I hope I made myself clear... thank you in advance!

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
programmingGirl
  • 107
  • 3
  • 11

1 Answers1

1

You can unpivot your table using a series of UNION ALL operations. Then simply select every row that contains a NULL-valued column:

SELECT resident_id, column_name
FROM (
  SELECT resident_id, 'resident_sex' AS column_name
  FROM mytable
  WHERE resident_sex IS NULL

  UNION ALL

  SELECT resident_id, 'race_code'
  FROM mytable
  WHERE race_code IS NULL

  UNION ALL

  SELECT resident_id, 'Resident_date_of_birth'
  FROM mytable
  WHERE Resident_date_of_birth IS NULL) AS t
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • this would be a good idea if there were only 3 columns, but I am working with many more columns, can be over 50... I wanted to know if there was any other way without doing each column separately... – programmingGirl Dec 08 '15 at 21:57
  • There is no other way I'm afraid using static SQL. You can try using prepared statements. – Giorgos Betsos Dec 08 '15 at 21:58
  • @programmingGirl Have a look [here](http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html) and [here](http://stackoverflow.com/questions/15507683/how-to-select-column-names-dynamically-in-mysql). – Giorgos Betsos Dec 08 '15 at 22:02