AGYMQAK6

1DROP TABLE IF EXISTS persyaratan;
2CREATE TABLE IF NOT EXISTS persyaratan(
3	id INT AUTO_INCREMENT PRIMARY KEY,
4	kode INT,
5	persyaratan VARCHAR(50)
6);
7INSERT INTO persyaratan(kode,persyaratan)
8VALUES
9(1,'KTP ASLI'),
10(1,'SIM ASLI'),
11(2,'FC KTP'),
12(2,'FC KK'),
13(2,'FC SIM');
14
15SELECT * FROM persyaratan;
16+----+------+-------------+
17| id | kode | persyaratan |
18+----+------+-------------+
19|  1 |    1 | KTP ASLI    |
20|  2 |    1 | SIM ASLI    |
21|  3 |    2 | FC KTP      |
22|  4 |    2 | FC KK       |
23|  5 |    2 | FC SIM      |
24+----+------+-------------+
255 rows in set (0.00 sec)
26
27
28DROP TABLE IF EXISTS formulir;
29CREATE TABLE IF NOT EXISTS formulir(
30	id INT AUTO_INCREMENT PRIMARY KEY,
31	kode INT,
32	kelengkapan VARCHAR(255)
33);
34
35INSERT INTO formulir(kode,kelengkapan)
36VALUES
37(1,'1,2'),
38(1,'1'),
39(2,'3,5'),
40(2,'3,4'),
41(2,'4');
42
43SELECT * FROM formulir;
44+----+------+-------------+
45| id | kode | kelengkapan |
46+----+------+-------------+
47|  1 |    1 | 1,2         |
48|  2 |    1 | 1           |
49|  3 |    2 | 3,5         |
50|  4 |    2 | 3,4         |
51|  5 |    2 | 4,5         |
52+----+------+-------------+
535 rows in set (0.00 sec)
54
55SELECT a.persyaratan
56FROM
57persyaratan a
58JOIN formulir b USING(kode)
59WHERE
60a.id NOT REGEXP CONCAT('^(', REPLACE( b.kelengkapan,  ',',  '|' ) ,  ')$')
61AND b.id=2;
62
63+-------------+
64| persyaratan |
65+-------------+
66| SIM ASLI    |
67+-------------+
681 row in set (0.00 sec)
69
70SELECT b.*,GROUP_CONCAT(a.id) AS id_kekurangan, GROUP_CONCAT(a.persyaratan) AS kekurangan
71FROM
72persyaratan a
73JOIN formulir b USING(kode)
74WHERE
75a.id NOT REGEXP CONCAT('^(', REPLACE( b.kelengkapan,  ',',  '|' ) ,  ')$')
76GROUP BY b.id;
77
78+----+------+-------------+---------------+---------------+
79| id | kode | kelengkapan | id_kekurangan | kekurangan    |
80+----+------+-------------+---------------+---------------+
81|  2 |    1 | 1           | 2             | SIM ASLI      |
82|  3 |    2 | 3,5         | 4             | FC KK         |
83|  4 |    2 | 3,4         | 5             | FC SIM        |
84|  5 |    2 | 4           | 5,3           | FC SIM,FC KTP |
85+----+------+-------------+---------------+---------------+
864 rows in set (0.01 sec)