query kekurangan persyaratan (serialized) 20170126001

DROP TABLE IF EXISTS persyaratan;
CREATE TABLE IF NOT EXISTS persyaratan(
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode INT,
    persyaratan VARCHAR(50)
);
INSERT INTO persyaratan(kode,persyaratan)
VALUES
(1,'KTP ASLI'),
(1,'SIM ASLI'),
(2,'FC KTP'),
(2,'FC KK'),
(2,'FC SIM');

SELECT * FROM persyaratan;
+----+------+-------------+
| id | kode | persyaratan |
+----+------+-------------+
|  1 |    1 | KTP ASLI    |
|  2 |    1 | SIM ASLI    |
|  3 |    2 | FC KTP      |
|  4 |    2 | FC KK       |
|  5 |    2 | FC SIM      |
+----+------+-------------+
5 rows in set (0.00 sec)


DROP TABLE IF EXISTS formulir;
CREATE TABLE IF NOT EXISTS formulir(
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode INT,
    kelengkapan VARCHAR(255)
);

INSERT INTO formulir(kode,kelengkapan)
VALUES
(1,'1,2'),
(1,'1'),
(2,'3,5'),
(2,'3,4'),
(2,'4');

SELECT * FROM formulir;
+----+------+-------------+
| id | kode | kelengkapan |
+----+------+-------------+
|  1 |    1 | 1,2         |
|  2 |    1 | 1           |
|  3 |    2 | 3,5         |
|  4 |    2 | 3,4         |
|  5 |    2 | 4,5         |
+----+------+-------------+
5 rows in set (0.00 sec)

SELECT a.persyaratan
FROM
persyaratan a
JOIN formulir b USING(kode)
WHERE
a.id NOT REGEXP CONCAT('^(', REPLACE( b.kelengkapan,  ',',  '|' ) ,  ')$')
AND b.id=2;

+-------------+
| persyaratan |
+-------------+
| SIM ASLI    |
+-------------+
1 row in set (0.00 sec)

SELECT b.*,GROUP_CONCAT(a.id) AS id_kekurangan, GROUP_CONCAT(a.persyaratan) AS kekurangan
FROM
persyaratan a
JOIN formulir b USING(kode)
WHERE
a.id NOT REGEXP CONCAT('^(', REPLACE( b.kelengkapan,  ',',  '|' ) ,  ')$')
GROUP BY b.id;

+----+------+-------------+---------------+---------------+
| id | kode | kelengkapan | id_kekurangan | kekurangan    |
+----+------+-------------+---------------+---------------+
|  2 |    1 | 1           | 2             | SIM ASLI      |
|  3 |    2 | 3,5         | 4             | FC KK         |
|  4 |    2 | 3,4         | 5             | FC SIM        |
|  5 |    2 | 4           | 5,3           | FC SIM,FC KTP |
+----+------+-------------+---------------+---------------+
4 rows in set (0.01 sec)