Read 231 times | Created 2013-10-03 08:09:08 | Updated 2013-10-03 08:09:08 | | |

 

DROP TABLE IF EXISTS `kategori`;  
CREATE TABLE IF NOT EXISTS `kategori` (
    `kd_kategori` CHAR(1) NOT NULL,
    `kategori` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`kd_kategori`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `kategori`
VALUES('W','Wanita'),('P','Pria');

DROP TABLE IF EXISTS `sub_kategori`;  
CREATE TABLE IF NOT EXISTS `sub_kategori` (
    `kd_sub_kategori` CHAR(2) NOT NULL,
    `sub_kategori` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`kd_sub_kategori`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;  

INSERT INTO `sub_kategori`
VALUES ('CJ','CELANA JEANS'),('CK','CELANA KAIN');

DROP TABLE IF EXISTS `barang`;  
CREATE TABLE IF NOT EXISTS `barang` (
    `id_barang` tinyint(3) ZEROFILL NOT NULL AUTO_INCREMENT,
    `kd_kategori` CHAR(1) NOT NULL,
    `kd_sub_kategori` CHAR(2) NOT NULL,
    `nama_barang` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`kd_kategori`,`kd_sub_kategori`,`id_barang`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;  

INSERT INTO `barang`(`kd_kategori`,`kd_sub_kategori`,`nama_barang`)
VALUES 
('W','CJ','Celana Jeans LEAK 28'),
('W','CJ','Celana Jeans LEAK 30'),
('P','CK','Celana Kain Eksekusi 34'),
('W','CJ','Celana Jeans LEAK 32'),
('P','CJ','Celana Jeans LEAK 30'),
('W','CJ','Celana Jeans LEPIS 32'),
('P','CJ','Celana Jeans LEAK 32'),
('P','CJ','Celana Jeans LEAK 34'),
('W','CJ','Celana Jeans LEPIS 30'),
('P','CJ','Celana Jeans LEPIS 34'),
('P','CK','Celana Kain Eksekusi 32'),
('P','CK','Celana Kain Eksekusi 30');

SELECT 
CONCAT(b.kd_kategori,b.kd_sub_kategori,b.id_barang) AS kode_barang,
b.nama_barang,
k.kategori,
s.sub_kategori
FROM
barang b
JOIN kategori k USING(kd_kategori)
JOIN sub_kategori s USING(kd_sub_kategori)
ORDER BY
b.kd_kategori,b.kd_sub_kategori,b.id_barang;