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;