iWvHYAew

1DROP TABLE IF EXISTS `kategori`;  
2CREATE TABLE IF NOT EXISTS `kategori` (
3    `kd_kategori` CHAR(1) NOT NULL,
4    `kategori` VARCHAR(100) NOT NULL,
5    PRIMARY KEY (`kd_kategori`)
6) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
7 
8INSERT INTO `kategori`
9VALUES('W','Wanita'),('P','Pria');
10 
11DROP TABLE IF EXISTS `sub_kategori`;  
12CREATE TABLE IF NOT EXISTS `sub_kategori` (
13    `kd_sub_kategori` CHAR(2) NOT NULL,
14    `sub_kategori` VARCHAR(100) NOT NULL,
15    PRIMARY KEY (`kd_sub_kategori`)
16) ENGINE=MyISAM  DEFAULT CHARSET=utf8;  
17 
18INSERT INTO `sub_kategori`
19VALUES ('CJ','CELANA JEANS'),('CK','CELANA KAIN');
20 
21DROP TABLE IF EXISTS `barang`;  
22CREATE TABLE IF NOT EXISTS `barang` (
23    `id_barang` tinyint(3) ZEROFILL NOT NULL AUTO_INCREMENT,
24    `kd_kategori` CHAR(1) NOT NULL,
25    `kd_sub_kategori` CHAR(2) NOT NULL,
26    `nama_barang` VARCHAR(100) NOT NULL,
27    PRIMARY KEY (`kd_kategori`,`kd_sub_kategori`,`id_barang`)
28) ENGINE=MyISAM  DEFAULT CHARSET=utf8;  
29 
30INSERT INTO `barang`(`kd_kategori`,`kd_sub_kategori`,`nama_barang`)
31VALUES 
32('W','CJ','Celana Jeans LEAK 28'),
33('W','CJ','Celana Jeans LEAK 30'),
34('P','CK','Celana Kain Eksekusi 34'),
35('W','CJ','Celana Jeans LEAK 32'),
36('P','CJ','Celana Jeans LEAK 30'),
37('W','CJ','Celana Jeans LEPIS 32'),
38('P','CJ','Celana Jeans LEAK 32'),
39('P','CJ','Celana Jeans LEAK 34'),
40('W','CJ','Celana Jeans LEPIS 30'),
41('P','CJ','Celana Jeans LEPIS 34'),
42('P','CK','Celana Kain Eksekusi 32'),
43('P','CK','Celana Kain Eksekusi 30');
44 
45SELECT 
46CONCAT(b.kd_kategori,b.kd_sub_kategori,b.id_barang) AS kode_barang,
47b.nama_barang,
48k.kategori,
49s.sub_kategori
50FROM
51barang b
52JOIN kategori k USING(kd_kategori)
53JOIN sub_kategori s USING(kd_sub_kategori)
54ORDER BY
55b.kd_kategori,b.kd_sub_kategori,b.id_barang;