Description:
Hi,
Execute
-------------------------------------------------------------------
DROP TABLE IF EXISTS `e`;
CREATE TABLE `e` (
`rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`scode` varchar(40) DEFAULT NULL,
`f_dt1` date DEFAULT NULL,
`f_dt2` date DEFAULT NULL,
PRIMARY KEY (`rid`),
KEY `f_dt1` (`f_dt1`),
KEY `f_dt2` (`f_dt2`),
KEY `scode` (`scode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `e` VALUES
(962328,'ABC1216',NULL,'2014-01-31'),
(962330,'ABC1217','2014-02-04','2014-02-04'),
(963122,'ABC1219',NULL,NULL),
(967451,'ABC1217',NULL,NULL);
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`scode` varchar(40) DEFAULT NULL,
`pcode` varchar(20) NOT NULL,
PRIMARY KEY (`rid`),
UNIQUE KEY `scode` (`scode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `s` VALUES
(300676, 'ABC1216','ABC'),
(300677, 'ABC1217','ABC'),
(300964, 'ABC1219','ABC');
SELECT s.scode,
concat(
ifnull(nullif(e1.f_dt2,"0000-00-00"),""),
"#",
ifnull(nullif(e1.f_dt1,"0000-00-00"),"")
) bad,
length(
concat(
ifnull(nullif(e1.f_dt2,"0000-00-00"),""),
"#",
ifnull(nullif(e1.f_dt1,"0000-00-00"),"")
)) bad_len,
concat(
ifnull(nullif(cast(e1.f_dt2 as char),"0000-00-00"),""),
"#",
ifnull(nullif(cast(e1.f_dt1 as char),"0000-00-00"),"")
) good,
length(
concat(
ifnull(nullif(cast(e1.f_dt2 as char),"0000-00-00"),""),
"#",
ifnull(nullif(cast(e1.f_dt1 as char),"0000-00-00"),"")
)) good_len
FROM s
LEFT JOIN e e1 ON e1.scode=s.scode
WHERE s.pcode = 'ABC'
GROUP BY s.scode
ORDER BY 1 DESC;
------------------------------------------------------------------------
Result are
+---------+---------+---------+-----------------------+----------+
| scode | bad | bad_len | good | good_len |
+---------+---------+---------+-----------------------+----------+
| ABC1219 | # | 1 | # | 1 |
| ABC1217 | 2014-02 | 21 | 2014-02-04#2014-02-04 | 21 |
| ABC1216 | 2014-01 | 11 | 2014-01-31# | 11 |
+---------+---------+---------+-----------------------+----------+
3 rows in set (0.08 sec)
"bad" column, of the last SQL statement, differ from "good" column results, while "bad_len" and "good_len" are the same.
Cast is used for "good" results. No cast for "bad".
How to repeat:
1. Execute supplied SQL commands
2. See the result