Read 416 times | Created 2014-07-23 07:18:21 | Updated 2014-08-03 13:48:06 | | |
DROP TABLE IF EXISTS `t_1`; CREATE TABLE IF NOT EXISTS `t_1` ( `kode` tinyint unsigned not null, `ket` char(1) not null ) ENGINE=MyISAM; INSERT INTO `t_1` VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'); SELECT * FROM t_1; +------+-----+ | kode | ket | +------+-----+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | | 5 | E | | 6 | F | | 7 | G | | 8 | H | | 9 | I | | 10 | J | | 11 | K | +------+-----+ 11 rows in set (0.00 sec) DROP TABLE IF EXISTS `t_2`; CREATE TABLE IF NOT EXISTS `t_2` ( `no` tinyint unsigned not null, `kode` varchar(100) not null ) ENGINE=MyISAM; INSERT INTO `t_2` VALUES(1,'1,2,3'),(2,'4,5,6,7'),(3,'8,9,10,11'); SELECT * FROM t_2; +----+---------+ | no | kode | +----+---------+ | 1 | 1,2,3 | | 2 | 4,5,6,7 | | 3 | 8,9,10,11 | +----+---------+ 3 rows in set (0.00 sec) SELECT t_2.no, GROUP_CONCAT( t_1.ket ) AS ket FROM t_2 JOIN t_1 ON t_1.kode REGEXP CONCAT( '^(', REPLACE( t_2.kode, ',', '|' ) , ')$' ) GROUP BY t_2.no; +----+---------+ | no | ket | +----+---------+ | 1 | A,B,C | | 2 | D,E,F,G | | 3 | H,I,J,K | +----+---------+ 3 rows in set (0.00 sec)