Read 270 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)