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!
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
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
