MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `inventori_dt1` (
  `_CODE` varchar(10) NOT NULL,
  `_ASSET` varchar(50) DEFAULT NULL,
  `_SERIAL` varchar(50) DEFAULT NULL,
  `_MODEL` varchar(10) DEFAULT NULL,
  `_DVCNM` varchar(50) DEFAULT NULL,
  `_IPADDR` varchar(50) DEFAULT NULL,
  `_IPTYPE` varchar(10) DEFAULT NULL,
  `_AD` varchar(10) DEFAULT NULL,
  `_WG` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
  `_CAT` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
  `_PROC` varchar(10) DEFAULT NULL,
  `_GRPC` varchar(10) DEFAULT NULL,
  `_RAM1_CAP` bigint DEFAULT NULL,
  `_RAM2_CAP` bigint DEFAULT NULL,
  `_RAM3_CAP` bigint DEFAULT NULL,
  `_RAM4_CAP` bigint DEFAULT NULL,
  `_HDD1_CAP` bigint DEFAULT NULL,
  `_HDD2_CAP` bigint DEFAULT NULL,
  `_HDD3_CAP` bigint DEFAULT NULL,
  `_HDD4_CAP` bigint DEFAULT NULL,
  `_SSD1_CAP` bigint DEFAULT NULL,
  `_SSD2_CAP` bigint DEFAULT NULL,
  `_SSD3_CAP` bigint DEFAULT NULL,
  `_SSD4_CAP` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventori_dt1` (`_CODE`, `_ASSET`, `_SERIAL`, `_MODEL`, `_DVCNM`, `_IPADDR`, `_IPTYPE`, `_AD`, `_WG`, `_CAT`, `_PROC`, `_GRPC`, `_RAM1_CAP`, `_RAM2_CAP`, `_RAM3_CAP`, `_RAM4_CAP`, `_HDD1_CAP`, `_HDD2_CAP`, `_HDD3_CAP`, `_HDD4_CAP`, `_SSD1_CAP`, `_SSD2_CAP`, `_SSD3_CAP`, `_SSD4_CAP`) VALUES
('IN2009002', 'FOQBEFUI32BF', 'EJ2FROE4F', '01130012', 'PC_MSHA', NULL, 'DINAMIK', 'ADA', 02, 01, '03150002', '02150001', 8000000000, NULL, NULL, NULL, 2000000000000, NULL, NULL, NULL, 480000000000, NULL, NULL, NULL),
('IN2009003', 'WF3QR12R32', 'SGH129F3', '01130022', 'PC_ALIA2', NULL, 'DINAMIK', 'ADA', 01, 01, '03150002', '02150001', 8000000000, NULL, NULL, NULL, 2000000000000, NULL, NULL, NULL, 480000000000, NULL, NULL, NULL),
('IN2009001', 'DJOBNW9EUFVBO23', 'PR29D33R', '01010006', 'PC-LAILA', NULL, 'DINAMIK', 'TIADA', 03, 01, '03150004', '02150001', 4000000000, NULL, NULL, NULL, 2000000000000, NULL, NULL, NULL, 480000000000, NULL, NULL, NULL);

CREATE TABLE `inventori_dt2` (
  `_CODE` varchar(10) NOT NULL,
  `_ASSET` varchar(50) DEFAULT NULL,
  `_SERIAL` varchar(50) DEFAULT NULL,
  `_MODEL` varchar(10) DEFAULT NULL,
  `_IPADDR` varchar(50) DEFAULT NULL,
  `_IPTYPE` varchar(10) DEFAULT NULL,
  `_CAT` int(2) UNSIGNED ZEROFILL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventori_dt2` (`_CODE`, `_ASSET`, `_SERIAL`, `_MODEL`, `_IPADDR`, `_IPTYPE`, `_CAT`) VALUES
('IN2009002', 'F23RTF23T', 'CNC342P000', '07130004', NULL, NULL, 07),
('IN2009002', '32RTG3G32', 'NPJA115896', '08080005', '112.12.43.85', NULL, 08),
('IN2009003', 'GF43T354Y34', 'CNC1270J4H', '07130004', NULL, NULL, 07),
('IN2009001', 'FNWOEBFOW31', '20TVMG2', '07010002', NULL, NULL, 07),
('IN2009001', '9382HR923FDI', 'CNCJF38364', '08080005', '112.12.43.119', NULL, 08);

CREATE TABLE `inventori_dt3` (
  `_CODE` varchar(10) NOT NULL,
  `_SERIAL` varchar(50) DEFAULT NULL,
  `_PROD` varchar(10) DEFAULT NULL,
  `_STTS` varchar(50) DEFAULT NULL,
  `_CAT` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
  `_RNW_DATE` datetime DEFAULT NULL,
  `_EXPY_DATE` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventori_dt3` (`_CODE`, `_SERIAL`, `_PROD`, `_STTS`, `_CAT`, `_RNW_DATE`, `_EXPY_DATE`) VALUES
('IN2009002', 'EJ2FROE4F', '16250001', NULL, 16, '1970-01-01 00:00:00', '1970-01-01 23:59:59'),
('IN2009002', 'EJ2FROE4F', '15180003', 'AKTIF', 15, '1970-01-01 00:00:00', '1970-01-01 23:59:59'),
('IN2009003', 'SGH129F3', '16250001', NULL, 16, '1970-01-01 00:00:00', '1970-01-01 23:59:59'),
('IN2009003', 'SGH129F3', '15180004', 'AKTIF', 15, '1970-01-01 00:00:00', '1970-01-01 23:59:59'),
('IN2009001', 'PR29D33R', '16250001', NULL, 16, '1970-01-01 00:00:00', '1970-01-01 23:59:59'),
('IN2009001', 'PR29D33R', '15180010', 'AKTIF', 15, '1970-01-01 00:00:00', '1970-01-01 23:59:59');

CREATE TABLE `inventori_mt` (
  `_CODE` varchar(10) NOT NULL,
  `_USERID` varchar(10) DEFAULT NULL,
  `_BRRW` varchar(10) DEFAULT NULL,
  `_STTS` varchar(50) DEFAULT NULL,
  `_OWSP` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
  `_CAT` int(2) UNSIGNED ZEROFILL DEFAULT NULL,
  `_CRTBY` varchar(10) DEFAULT NULL,
  `_UPDBY` varchar(10) DEFAULT NULL,
  `_CRTON` datetime DEFAULT CURRENT_TIMESTAMP,
  `_UPDON` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inventori_mt` (`_CODE`, `_USERID`, `_BRRW`, `_STTS`, `_OWSP`, `_CAT`, `_CRTBY`, `_UPDBY`, `_CRTON`, `_UPDON`) VALUES
('IN2009001', '10200', NULL, 'AKTIF', 01, 01, 'admin', NULL, '2020-09-02 15:59:57', '2020-09-10 10:52:20'),
('IN2009002', '10895', NULL, 'AKTIF', 01, 01, 'admin', NULL, '2020-09-02 16:06:56', '2020-09-10 10:32:09'),
('IN2009003', '10070', NULL, 'AKTIF', 01, 01, 'admin', NULL, '2020-09-02 16:07:37', '2020-09-10 10:51:59');

CREATE TABLE `kod_bahagian` (
  `_CODE` varchar(10) NOT NULL,
  `_PTKOD` varchar(10) NOT NULL,
  `_PKKOD` varchar(10) NOT NULL,
  `_DESC` varchar(255) DEFAULT NULL,
  `_USERID` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_bahagian` (`_CODE`, `_PTKOD`, `_PKKOD`, `_DESC`, `_USERID`) VALUES
('0200', '02', '00', 'DEPT1', NULL),
('0210', '02', '10', 'DEPT2', NULL),
('0220', '02', '20', 'DEPT3', NULL);

CREATE TABLE `kod_jawatan` (
  `_CODE` varchar(10) NOT NULL,
  `_DESC` varchar(255) DEFAULT NULL,
  `_GRED` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_jawatan` (`_CODE`, `_DESC`, `_GRED`) VALUES
('1021', 'IT OFFICER', 'M41'),
('1027', 'HR OFFICER', 'M41'),
('1085', 'CLERK', 'N19');

CREATE TABLE `kod_kategori` (
  `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_DESC` varchar(255) NOT NULL,
  `_TYPE` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_kategori` (`_CODE`, `_DESC`, `_TYPE`) VALUES
(01, 'CPU', 'HW'),
(02, 'GPU', 'HW'),
(03, 'PROCESSOR', 'HW'),
(07, 'MONITOR', 'HW'),
(08, 'PRINTER', 'HW'),
(10, 'PROJECTOR', 'HW'),
(15, 'OPERATING SYSTEM', 'SW'),
(16, 'ANTI-VIRUS', 'SW');

CREATE TABLE `kod_milikan` (
  `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_DESC` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_milikan` (`_CODE`, `_DESC`) VALUES
(01, 'BELI'),
(02, 'SEWA');

CREATE TABLE `kod_model` (
  `_CODE` varchar(10) NOT NULL,
  `_MDL` int(4) UNSIGNED ZEROFILL NOT NULL,
  `_MFR` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_CAT` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_DESC` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_model` (`_CODE`, `_MDL`, `_MFR`, `_CAT`, `_DESC`) VALUES
('01010006', 0006, 01, 01, 'Veriton S Series'),
('01130012', 0012, 13, 01, 'Pavilion'),
('01130022', 0022, 13, 01, 'ProDesk'),
('02150001', 0001, 15, 02, 'UHD 630'),
('03150001', 0001, 15, 03, 'Core i9-10900'),
('03150002', 0002, 15, 03, 'Core i9-9900'),
('03150003', 0003, 15, 03, 'Core i7-10700'),
('03150004', 0004, 15, 03, 'Core i7-9700'),
('07010002', 0002, 01, 07, 'LCD Monitor'),
('07130004', 0004, 13, 07, 'Flat Panel Monitor'),
('08080001', 0001, 08, 08, 'imageCLASS LBP214dw'),
('08080005', 0005, 08, 08, 'i-SENSYS LBP6300dn'),
('08080006', 0006, 08, 08, 'i-SENSYS LBP7200Cdn');

CREATE TABLE `kod_pengilang` (
  `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_DESC` varchar(255) NOT NULL,
  `_TYPE` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_pengilang` (`_CODE`, `_DESC`, `_TYPE`) VALUES
(01, 'ACER', 'HW'),
(08, 'CANON', 'HW'),
(11, 'ESET', 'SW'),
(13, 'HP', 'HW'),
(15, 'INTEL', 'HW'),
(18, 'MICROSOFT', 'BOTH'),
(25, 'TREND MICRO', 'SW');

CREATE TABLE `kod_produk` (
  `_CODE` varchar(10) NOT NULL,
  `_PRD` int(4) UNSIGNED ZEROFILL NOT NULL,
  `_MFR` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_CAT` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_DESC` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_produk` (`_CODE`, `_PRD`, `_MFR`, `_CAT`, `_DESC`) VALUES
('15180003', 0003, 18, 15, 'WINDOWS 7 (32BIT)'),
('15180004', 0004, 18, 15, 'WINDOWS 7 (64BIT)'),
('15180009', 0009, 18, 15, 'WINDOWS 10 (32BIT)'),
('15180010', 0010, 18, 15, 'WINDOWS 10 (64BIT)'),
('16110001', 0001, 11, 16, 'ENDPOINT ANTIVIRUS'),
('16250001', 0001, 25, 16, 'ANTIVIRUS');

CREATE TABLE `kod_workgroup` (
  `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL,
  `_DESC` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `kod_workgroup` (`_CODE`, `_DESC`) VALUES
(01, 'myLocalNet'),
(02, 'WORKGROUP'),
(03, 'LAIN-LAIN');

CREATE TABLE `sys_capaian` (
  `_GROUP` varchar(255) NOT NULL,
  `_MODULE` varchar(255) NOT NULL,
  `_ROLE` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sys_kumpulan` (
  `_GROUP` varchar(255) NOT NULL,
  `_DESC` varchar(255) DEFAULT NULL,
  `_UPD_ON` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sys_kumpulan` (`_GROUP`, `_DESC`, `_UPD_ON`) VALUES
('ADMIN', 'No description', '2020-09-10 10:56:28'),
('GUEST', 'No description', '2020-09-10 10:56:39'),
('TECHNICIAN', 'No description', '2020-09-10 10:55:47'),
('USER', 'No description', '2020-09-10 10:56:03');

CREATE TABLE `sys_pengguna` (
  `_USERID` varchar(10) NOT NULL,
  `_NAME` varchar(255) DEFAULT NULL,
  `_GROUP` varchar(255) DEFAULT NULL,
  `_POSTN` varchar(10) DEFAULT NULL,
  `_PTKOD` varchar(10) DEFAULT NULL,
  `_PKKOD` varchar(10) DEFAULT NULL,
  `_STATK` varchar(10) DEFAULT NULL,
  `_PASS` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `_UPD_ON` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sys_pengguna` (`_USERID`, `_NAME`, `_GROUP`, `_POSTN`, `_PTKOD`, `_PKKOD`, `_STATK`, `_PASS`, `_UPD_ON`) VALUES
('10070', 'ALIA', 'USER', '1027', '02', '10', 'Y', 'WFRD11lmQzdxWX6GYnh4azFLMWFwZ2d3', '2020-09-10 10:29:39'),
('10120', 'SHAMSUL', 'TECHNICIAN', '1021', '02', '00', 'Y', 'S01LQjBuUWMxaE9xWn40TFNYRHVjUT09', '2020-09-10 10:29:37'),
('10200', 'LAILA', 'USER', '1085', '02', '10', 'Y', 'WFRDR1lmQzdxWG1PYpo2azFLMeefaZz09', '2020-09-10 10:29:33'),
('10895', 'MASHA', 'USER', '1085', '02', '10', 'Y', 'WFRDR1lmDtEdWG1PYnh4wwFLMWFaZz01', '2020-09-10 10:29:35'),
('admin', 'ADMINSTRATOR', 'ADMIN', NULL, '02', '20', NULL, 'WW1IdUg4NlQwMlpGTUhIUVRXWEdiUT09', '2020-09-10 10:29:42');

ALTER TABLE `inventori_dt1`
  ADD KEY `_CODE` (`_CODE`) USING BTREE,
  ADD KEY `_MODEL` (`_MODEL`) USING BTREE,
  ADD KEY `_PROC` (`_PROC`) USING BTREE,
  ADD KEY `_GRPC` (`_GRPC`) USING BTREE,
  ADD KEY `_WG` (`_WG`) USING BTREE,
  ADD KEY `_CAT` (`_CAT`) USING BTREE,
  ADD KEY `_SERIAL` (`_SERIAL`) USING BTREE;

ALTER TABLE `inventori_dt2`
  ADD KEY `_CODE` (`_CODE`) USING BTREE,
  ADD KEY `_MODEL` (`_MODEL`) USING BTREE,
  ADD KEY `_CAT` (`_CAT`) USING BTREE;

ALTER TABLE `inventori_dt3`
  ADD KEY `_CODE` (`_CODE`) USING BTREE,
  ADD KEY `_CAT` (`_CAT`) USING BTREE,
  ADD KEY `_PROD` (`_PROD`) USING BTREE,
  ADD KEY `_SERIAL` (`_SERIAL`) USING BTREE;

ALTER TABLE `inventori_mt`
  ADD PRIMARY KEY (`_CODE`) USING BTREE,
  ADD KEY `_USERID` (`_USERID`) USING BTREE,
  ADD KEY `_OWSP` (`_OWSP`) USING BTREE;

ALTER TABLE `kod_bahagian`
  ADD PRIMARY KEY (`_CODE`) USING BTREE;

ALTER TABLE `kod_jawatan`
  ADD PRIMARY KEY (`_CODE`) USING BTREE;

ALTER TABLE `kod_kategori`
  ADD PRIMARY KEY (`_CODE`) USING BTREE;

ALTER TABLE `kod_milikan`
  ADD PRIMARY KEY (`_CODE`) USING BTREE;

ALTER TABLE `kod_model`
  ADD PRIMARY KEY (`_CODE`) USING BTREE,
  ADD KEY `_MFR` (`_MFR`) USING BTREE,
  ADD KEY `_CAT` (`_CAT`) USING BTREE;

ALTER TABLE `kod_pengilang`
  ADD PRIMARY KEY (`_CODE`) USING BTREE;

ALTER TABLE `kod_produk`
  ADD PRIMARY KEY (`_CODE`) USING BTREE,
  ADD KEY `_MFR` (`_MFR`) USING BTREE,
  ADD KEY `_CAT` (`_CAT`) USING BTREE;

ALTER TABLE `kod_workgroup`
  ADD PRIMARY KEY (`_CODE`) USING BTREE;

ALTER TABLE `sys_capaian`
  ADD KEY `_GROUP` (`_GROUP`) USING BTREE;

ALTER TABLE `sys_kumpulan`
  ADD PRIMARY KEY (`_GROUP`) USING BTREE,
  ADD UNIQUE KEY `_GROUP` (`_GROUP`) USING BTREE;

ALTER TABLE `sys_pengguna`
  ADD PRIMARY KEY (`_USERID`) USING BTREE,
  ADD UNIQUE KEY `_USERID` (`_USERID`) USING BTREE,
  ADD KEY `_GROUP` (`_GROUP`) USING BTREE;

ALTER TABLE `kod_kategori`
  MODIFY `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

ALTER TABLE `kod_milikan`
  MODIFY `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `kod_pengilang`
  MODIFY `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32;

ALTER TABLE `kod_workgroup`
  MODIFY `_CODE` int(2) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `inventori_dt1`
  ADD CONSTRAINT `inventori_dt1_ibfk_1` FOREIGN KEY (`_CODE`) REFERENCES `inventori_mt` (`_CODE`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt1_ibfk_2` FOREIGN KEY (`_MODEL`) REFERENCES `kod_model` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt1_ibfk_3` FOREIGN KEY (`_PROC`) REFERENCES `kod_model` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt1_ibfk_4` FOREIGN KEY (`_GRPC`) REFERENCES `kod_model` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt1_ibfk_5` FOREIGN KEY (`_WG`) REFERENCES `kod_workgroup` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt1_ibfk_6` FOREIGN KEY (`_CAT`) REFERENCES `kod_kategori` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `inventori_dt2`
  ADD CONSTRAINT `inventori_dt2_ibfk_1` FOREIGN KEY (`_CODE`) REFERENCES `inventori_mt` (`_CODE`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt2_ibfk_2` FOREIGN KEY (`_MODEL`) REFERENCES `kod_model` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt2_ibfk_3` FOREIGN KEY (`_CAT`) REFERENCES `kod_kategori` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `inventori_dt3`
  ADD CONSTRAINT `inventori_dt3_ibfk_1` FOREIGN KEY (`_CODE`) REFERENCES `inventori_mt` (`_CODE`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt3_ibfk_2` FOREIGN KEY (`_CAT`) REFERENCES `kod_kategori` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt3_ibfk_3` FOREIGN KEY (`_PROD`) REFERENCES `kod_produk` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_dt3_ibfk_4` FOREIGN KEY (`_SERIAL`) REFERENCES `inventori_dt1` (`_SERIAL`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `inventori_mt`
  ADD CONSTRAINT `inventori_mt_ibfk_1` FOREIGN KEY (`_USERID`) REFERENCES `sys_pengguna` (`_USERID`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `inventori_mt_ibfk_2` FOREIGN KEY (`_OWSP`) REFERENCES `kod_milikan` (`_CODE`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `kod_model`
  ADD CONSTRAINT `kod_model_ibfk_1` FOREIGN KEY (`_MFR`) REFERENCES `kod_pengilang` (`_CODE`) ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT `kod_model_ibfk_2` FOREIGN KEY (`_CAT`) REFERENCES `kod_kategori` (`_CODE`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `kod_produk`
  ADD CONSTRAINT `kod_produk_ibfk_1` FOREIGN KEY (`_MFR`) REFERENCES `kod_pengilang` (`_CODE`) ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT `kod_produk_ibfk_2` FOREIGN KEY (`_CAT`) REFERENCES `kod_kategori` (`_CODE`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `sys_capaian`
  ADD CONSTRAINT `sys_capaian_ibfk_1` FOREIGN KEY (`_GROUP`) REFERENCES `sys_kumpulan` (`_GROUP`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `sys_pengguna`
  ADD CONSTRAINT `sys_pengguna_ibfk_1` FOREIGN KEY (`_GROUP`) REFERENCES `sys_kumpulan` (`_GROUP`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

Records: 3  Duplicates: 0  Warnings: 0

Records: 5  Duplicates: 0  Warnings: 0

Records: 6  Duplicates: 0  Warnings: 0

Records: 3  Duplicates: 0  Warnings: 0

Records: 3  Duplicates: 0  Warnings: 0

Records: 3  Duplicates: 0  Warnings: 0

Records: 8  Duplicates: 0  Warnings: 0

Records: 2  Duplicates: 0  Warnings: 0

Records: 13  Duplicates: 0  Warnings: 0

Records: 7  Duplicates: 0  Warnings: 0

Records: 6  Duplicates: 0  Warnings: 0

Records: 3  Duplicates: 0  Warnings: 0

Records: 4  Duplicates: 0  Warnings: 0

Records: 5  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

The table 'inventori_mt' is full

SELECT JSON_PRETTY( JSON_ARRAYAGG(Z) ) AS Z
  FROM
  (
  SELECT 
    JSON_OBJECT(
                '_CODE', a._CODE,
                '_USERID', a._USERID,
                '_NAME', c._NAME,
                'HAHA', 
                (SELECT 
                JSON_ARRAYAGG(
                JSON_OBJECT(
                '_CAT', b._CAT,
                '_MODEL', b._MODEL,
                '_SERIAL', b._SERIAL,
                '_IPADDR', b._IPADDR ,
                '_OS', (SELECT _DESC 
                          FROM kod_produk 
                         WHERE _CODE = (SELECT _PROD 
                                          FROM inventori_dt3 
                                         WHERE _CODE = a._CODE 
                                           AND _CAT = '15' 
                                           AND _SERIAL = b._SERIAL)),
                 '_AV', (SELECT CONCAT(_DESC, 
                                       ' (', 
                                      (CASE WHEN(SELECT _CODE 
                                                   FROM inventori_dt3 
                                                  WHERE _CODE = a._CODE 
                                                   AND _CAT = '16' 
                                                   AND _EXPY_DATE > NOW()) 
                                                IS NOT NULL 
                                            THEN 
                                                 'AKTIF' 
                                            ELSE 
                                                 'TAMAT' 
                                             END), ')') 
                           FROM kod_produk 
                          WHERE _CODE = (SELECT _PROD 
                                           FROM inventori_dt3 
                                          WHERE _CODE = a._CODE 
                                            AND _CAT = '16' 
                                            AND _SERIAL = b._SERIAL)) 
                                         )
                               )
                           )
                 ) Z
  FROM inventori_mt a
  LEFT JOIN
       (
        SELECT _CODE, _CAT, _MODEL, _SERIAL, _IPADDR FROM inventori_dt1
        UNION ALL
        SELECT _CODE, _CAT, _MODEL, _SERIAL, _IPADDR FROM inventori_dt2 
       ) b 
         ON a._CODE = b._CODE
  LEFT JOIN sys_pengguna c 
         ON a._USERID = c._USERID
 GROUP BY a._CODE, a._USERID, c._NAME   
 ) q

Z
[ { "HAHA": [ { "_AV": "ANTIVIRUS (TAMAT)", "_OS": "WINDOWS 10 (64BIT)", "_CAT": 1, "_MODEL": "01010006", "_IPADDR": null, "_SERIAL": "PR29D33R" }, { "_AV": null, "_OS": null, "_CAT": 8, "_MODEL": "08080005", "_IPADDR": "112.12.43.119", "_SERIAL": "CNCJF38364" }, { "_AV": null, "_OS": null, "_CAT": 7, "_MODEL": "07010002", "_IPADDR": null, "_SERIAL": "20TVMG2" } ], "_CODE": "IN2009001", "_NAME": "LAILA", "_USERID": "10200" }, { "HAHA": [ { "_AV": null, "_OS": null, "_CAT": 8, "_MODEL": "08080005", "_IPADDR": "112.12.43.85", "_SERIAL": "NPJA115896" }, { "_AV": null, "_OS": null, "_CAT": 7, "_MODEL": "07130004", "_IPADDR": null, "_SERIAL": "CNC342P000" }, { "_AV": "ANTIVIRUS (TAMAT)", "_OS": "WINDOWS 7 (32BIT)", "_CAT": 1, "_MODEL": "01130012", "_IPADDR": null, "_SERIAL": "EJ2FROE4F" } ], "_CODE": "IN2009002", "_NAME": "MASHA", "_USERID": "10895" }, { "HAHA": [ { "_AV": "ANTIVIRUS (TAMAT)", "_OS": "WINDOWS 7 (64BIT)", "_CAT": 1, "_MODEL": "01130022", "_IPADDR": null, "_SERIAL": "SGH129F3" }, { "_AV": null, "_OS": null, "_CAT": 7, "_MODEL": "07130004", "_IPADDR": null, "_SERIAL": "CNC1270J4H" } ], "_CODE": "IN2009003", "_NAME": "ALIA", "_USERID": "10070" } ]

[
{
"HAHA": [
{
"_AV": "ANTIVIRUS (TAMAT)",
"_OS": "WINDOWS 10 (64BIT)",
"_CAT": 1,
"_MODEL": "01010006",
"_IPADDR": null,
"_SERIAL": "PR29D33R"
},
{
"_AV": null,
"_OS": null,
"_CAT": 8,
"_MODEL": "08080005",
"_IPADDR": "112.12.43.119",
"_SERIAL": "CNCJF38364"
},
{
"_AV": null,
"_OS": null,
"_CAT": 7,
"_MODEL": "07010002",
"_IPADDR": null,
"_SERIAL": "20TVMG2"
}
],
"_CODE": "IN2009001",
"_NAME": "LAILA",
"_USERID": "10200"
},
{
"HAHA": [
{
"_AV": null,
"_OS": null,
"_CAT": 8,
"_MODEL": "08080005",
"_IPADDR": "112.12.43.85",
"_SERIAL": "NPJA115896"
},
{
"_AV": null,
"_OS": null,
"_CAT": 7,
"_MODEL": "07130004",
"_IPADDR": null,
"_SERIAL": "CNC342P000"
},
{
"_AV": "ANTIVIRUS (TAMAT)",
"_OS": "WINDOWS 7 (32BIT)",
"_CAT": 1,
"_MODEL": "01130012",
"_IPADDR": null,
"_SERIAL": "EJ2FROE4F"
}
],
"_CODE": "IN2009002",
"_NAME": "MASHA",
"_USERID": "10895"
},
{
"HAHA": [
{
"_AV": "ANTIVIRUS (TAMAT)",
"_OS": "WINDOWS 7 (64BIT)",
"_CAT": 1,
"_MODEL": "01130022",
"_IPADDR": null,
"_SERIAL": "SGH129F3"
},
{
"_AV": null,
"_OS": null,
"_CAT": 7,
"_MODEL": "07130004",
"_IPADDR": null,
"_SERIAL": "CNC1270J4H"
}
],
"_CODE": "IN2009003",
"_NAME": "ALIA",
"_USERID": "10070"
}
]