CODEWITHSUNDEEP
mysqlsqlnull
Pink Ninja
Pink Ninja

Reputation: 157

Select only columns with null/empty values from the table

I have a data set that looks like this but columns goes on as data4, data5 up to data20

id      data1       data2       data3
(int)   (varchar)   (int)       (date)
-------------------------------------
1       xyz         (null)      0000-00-00
2       (empty)     321         2013-09-02
3       abc         555         2013-02-29
4       def         (null)      2013-09-02
5       lmn         678         2013-03-19

I only want to select the rows that has columns with null or empty values so the customer can decide which fields are needed to be filled in their tickets. 

data1       data2       data3
(varchar)   (int)       (date)
-------------------------------------
2            1,4          1,5

Or if anyone could think of a better variation? I am open to anything at the moment as long as it is doing the work I want. Is this even possible? Thanks!

Upvotes: 1

Views: 6075

Answers (2)

peterm
peterm

Reputation: 92785

You can do it with dynamic SQL like this

DELIMITER $$
CREATE PROCEDURE sp_empty()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(
           CONCAT('(SELECT GROUP_CONCAT(id) FROM Table1 WHERE `', 
                  column_name, '` IS NULL ',
                  CASE  
                    WHEN data_type IN('varchar', 'char') 
                      THEN CONCAT('OR `', column_name, '` = ''''')
                    WHEN data_type IN('date', 'datetime', 'time') 
                      THEN CONCAT('OR `', column_name, '` = 0')
                    ELSE ''
                  END, ')`', column_name, '`'))
     INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
     AND table_name = 'table1'
     AND column_name NOT IN ('id')
   GROUP BY table_name;

  SET @sql = CONCAT('SELECT ', @sql);

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Note that it takes care of different data types of columns.

Sample usage:

CALL sp_empty();

Output:

| DATA1 | DATA2 | DATA3 |
|-------|-------|-------|
|     2 |   1,4 |     1 |

Here is SQLFiddle demo

You obviously don't necessarily have to use a stored procedure. It's just simplifies things on the calling end. You can do

SET @sql = NULL;

SELECT GROUP_CONCAT(
         CONCAT('(SELECT GROUP_CONCAT(id) FROM Table1 WHERE `', 
                column_name, '` IS NULL ',
                CASE  
                  WHEN data_type IN('varchar', 'char') 
                    THEN CONCAT('OR `', column_name, '` = ''''')
                  WHEN data_type IN('date', 'datetime', 'time') 
                    THEN CONCAT('OR `', column_name, '` = 0')
                  ELSE ''
                END, ')`', column_name, '`'))
   INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = 'table1'
   AND column_name NOT IN ('id')
 GROUP BY table_name;

SET @sql = CONCAT('SELECT ', @sql);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

If for some reason you can't use a version with dynamic SQL, then as a last resort you can produce a query like this on your own including all of your columns

SELECT 
(
  SELECT GROUP_CONCAT(id) 
     FROM Table1 
    WHERE `data1` IS NULL 
       OR `data1` = ''
) `data1`,
(
  SELECT GROUP_CONCAT(id) 
    FROM Table1 
   WHERE `data2` IS NULL
) `data2`,
(
  SELECT GROUP_CONCAT(id) 
    FROM Table1 
   WHERE `data3` IS NULL 
      OR `data3` = 0
) `data3`
...

Here is SQLFiddle demo

Upvotes: 2

sunghee
sunghee

Reputation: 424

This would probably be a bad idea considering you have up to 20 data columns, but would something like this work?

SELECT * FROM
    (SELECT GROUP_CONCAT(id) data1 FROM foo WHERE data1 IS NULL OR data1 = '') AS f1,
    (SELECT GROUP_CONCAT(id) data2 FROM foo WHERE data2 IS NULL OR data2 = '') AS f2,
    (SELECT GROUP_CONCAT(id) data3 FROM foo WHERE data3 IS NULL OR data3 = '') AS f3

Upvotes: 1

Related Questions