Non-null data extraction from MySQL database table version 8.0.17

user_15117016 0
Viewed 22

This the structure and data of wtable on database MySql version 8.0.17

DROP TABLE IF EXISTS `wtable`;
CREATE TABLE `wtable`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `img` varchar(255) DEFAULT NULL,
  `ppt` varchar(255) DEFAULT NULL,
  `xls` varchar(255) DEFAULT NULL,
  `pdf` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of wtable
-- ----------------------------
INSERT INTO `wtable` VALUES (1, NULL, 'file.ppt', NULL, 'file.pdf');
INSERT INTO `wtable` VALUES (2, NULL, NULL, 'file.xls', NULL);
INSERT INTO `wtable` VALUES (3, 'file.jpg', NULL, NULL, NULL);

I need extract from wtable all rows populated and this is my query

SELECT
    img,
    ppt,
    xls,
    pdf 
FROM
    `wtable` 
WHERE
    ( img IS NOT NULL OR ppt IS NOT NULL OR xls IS NOT NULL OR pdf IS NOT NULL );

+----------+----------+----------+----------+
| img      | ppt      | xls      | pdf      |
+----------+----------+----------+----------+
| NULL     | file.ppt | NULL     | file.pdf |
| NULL     | NULL     | file.xls | NULL     |
| file.jpg | NULL     | NULL     | NULL     |
+----------+----------+----------+----------+
3 rows in set (0.02 sec)

But I need this output... any suggestion?

+----------+
| Allfiles |
+----------+
| file.ppt |
| file.pdf |
| file.xls |
| file.jpg |
+----------+

Help me to do it

mysql
1 Answers

You need UNION

SELECT    img allfiles
FROM        `wtable`
WHERE  img IS NOT NULL 
UNION ALL 
SELECT      ppt
FROM        `wtable`
WHERE  ppt IS NOT NULL 
UNION ALL 
SELECT      pdf
FROM        `wtable`
WHERE  pdf IS NOT NULL 
UNION ALL 
SELECT      xls
FROM        `wtable`
WHERE  xls IS NOT NULL

use UNION for distinct result or UNION ALL for gent also duplicated rows

user_35223120
Related