General Articles

Join Table

Mengambil Data dari beberapa Table di MySQL

Menggabungkan jumlah dari suatu nilai field pada beberapa tabel dapat dilakukan dengan menggunakan query aggregate SUM() dan GROUP BY dengan menggunakan klausa JOIN

author : cahya dsn, published on : October 7th, 2013 updated on : November 2nd, 2014

minerva minerva donasi donation

Mau lihat artikel lainya? Dapatkan artikel-artikel lain seputar pemrograman website di sini, dan dapatkan ide-ide baru

Pendahuluan

Menggabungkan jumlah dari suatu nilai field pada beberapa tabel dapat dilakukan dengan menggunakan query aggregate SUM() dan GROUP BY dengan menggunakan klausa JOIN

Contoh Kasus

Menampilkan jumlah nilai suatu field dari tiga tabel yang identik dengan berdasarkan kondisi tertentu

Permasalahan

Kita mempunyai 3 buat tabel, masing-masing 'tbl_bongkar','tbl_kemas', dan 'tbl_muat' yang struktur tabelnya identik sebagai berikut:

tbl_bongkar:

+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung terigu  | 2013-10-01 |     20 |
| Tepung kanji   | 2013-10-01 |     30 |
| Tepung maizena | 2013-10-02 |     40 |
| Tepung maizena | 2013-10-03 |     25 |
| Tepung kanji   | 2013-10-04 |     20 |
+----------------+------------+--------+

tbl_kemas:

+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung terigu  | 2013-10-01 |     10 |
| Tepung kanji   | 2013-10-01 |     20 |
| Tepung kanji   | 2013-10-03 |     10 |
| Tepung maizena | 2013-10-03 |     10 |
+----------------+------------+--------+

tbl_muat:

+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung kanji   | 2013-10-02 |     10 |
| Tepung terigu  | 2013-10-02 |     10 |
| Tepung maizena | 2013-10-04 |      5 |
+----------------+------------+--------+

Semisal kita menginginkan laporan berdasarkan data dari tanggal 1 s.d. 3 Oktober 2013 dalam bentuk sebagai berikut:

+----------------+-------------+-----------+----------+
| jenis          | jml_bongkar | jml_kemas | jml_muat |
+----------------+-------------+-----------+----------+
| Tepung kanji   |          30 |        30 | 10       |
| Tepung maizena |          65 |        10 |          |
| Tepung terigu  |          20 |        10 | 10       |
+----------------+-------------+-----------+----------+

Persiapan Data

Untuk pembelajaran kita buat dulu tabel-tabelnya beserta isinya (dalam hal ini kita gunakan database 'test') sebagai berikut: 

USE test;

DROP TABLE IF EXISTS `tbl_bongkar`; 
CREATE TABLE IF NOT EXISTS `tbl_bongkar` ( 
  `jenis` VARCHAR(25)NOT NULL, 
  `tgl` DATE NOT NULL,
  `jumlah` INT(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

INSERT INTO tbl_bongkar
VALUES
('Tepung terigu','2013-10-01',20),
('Tepung kanji','2013-10-01',30),
('Tepung maizena','2013-10-02',40),
('Tepung maizena','2013-10-03',25),
('Tepung kanji','2013-10-04',20);

DROP TABLE IF EXISTS `tbl_kemas`; 
CREATE TABLE IF NOT EXISTS `tbl_kemas` ( 
  `jenis` VARCHAR(25)NOT NULL, 
  `tgl` DATE NOT NULL,
  `jumlah` INT(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

INSERT INTO tbl_kemas
VALUES
('Tepung terigu','2013-10-01',10),
('Tepung kanji','2013-10-01',20),
('Tepung kanji','2013-10-03',10),
('Tepung maizena','2013-10-03',10);

DROP TABLE IF EXISTS `tbl_muat`; 
CREATE TABLE IF NOT EXISTS `tbl_muat` ( 
  `jenis` VARCHAR(25)NOT NULL, 
  `tgl` DATE NOT NULL,
  `jumlah` INT(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

INSERT INTO tbl_muat
VALUES
('Tepung kanji','2013-10-02',10),
('Tepung terigu','2013-10-02',10),
('Tepung maizena','2013-10-04',5);

Memeriksa Data

Data yang sudah dibuat dan diisi sebelumnya bisa kita cek dengan query SELECT sederhana, masing-masing sebagai berikut: 

SELECT * FROM tbl_bongkar;
SELECT * FROM tbl_kemas;
SELECT * FROM tbl_muat;

Dari query-query tersebut diperoleh, masing-masing data sebagai berikut:

tabel tbl_bongkar

+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung terigu  | 2013-10-01 |     20 |
| Tepung kanji   | 2013-10-01 |     30 |
| Tepung maizena | 2013-10-02 |     40 |
| Tepung maizena | 2013-10-03 |     25 |
| Tepung kanji   | 2013-10-04 |     20 |
+----------------+------------+--------+
tabel tbl_kemas
+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung terigu  | 2013-10-01 |     10 |
| Tepung kanji   | 2013-10-01 |     20 |
| Tepung kanji   | 2013-10-03 |     10 |
| Tepung maizena | 2013-10-03 |     10 |
+----------------+------------+--------+
tabel tbl_muat
+----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung kanji   | 2013-10-02 |     10 |
| Tepung terigu  | 2013-10-02 |     10 |
| Tepung maizena | 2013-10-04 |      5 |
+----------------+------------+--------+

Penyelesaian

Query Dasar

Total jumlah suatu data pada suatu field dalam tabel dapat dicari dengan menggunakan fungsi aggregate SUM() pada field yang nilainya dijumlahkan, dan digabungkan dengan klausa GROUP BY untuk mengelompokkan data berdasarkan field yang menjadi acuan. Untuk mendapatkan data pada rentang waktu tertentu berdasarkan suatu field digunakan comparison operator. BETWEEN ... AND ... . Ekspresi field BETWEEN kondisi1 AND kondisi2 adalah setara dengan pernyataan kondisi1 <= field AND field <= kondisi2

  1. Untuk mendapatkan data total jumlah yang di 'bongkar' berdasarkan tabel 'tbl_bongkar' untuk tanggal 1 s.d 3 Oktober 2013 bisa dibuatkan query[1] sebagai berikut: 

    SELECT 
 jenis,
 SUM(jumlah) AS jml_bongkar
FROM 
 tbl_bongkar 
WHERE 
 tgl BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY
 jenis;

    Hasil yang diperoleh dari query tersebut adalah: 

    +----------------+-------------+
| jenis          | jml_bongkar |
+----------------+-------------+
| Tepung kanji   |          30 |
| Tepung maizena |          65 |
| Tepung terigu  |          20 |
+----------------+-------------+

  2. Untuk mendapatkan data total jumlah yang di 'kemas' berdasarkan tabel 'tbl_kemas' untuk tanggal 1 s.d 3 Oktober 2013 bisa dibuatkan query[2] sebagai berikut: 

    SELECT 
 jenis,
 SUM(jumlah) AS jml_kemas
FROM 
 tbl_kemas 
WHERE 
 tgl BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY
 jenis;

    hasil yang didapat dari query itu adalah: 

    +----------------+-----------+
| jenis          | jml_kemas |
+----------------+-----------+
| Tepung kanji   |        30 |
| Tepung maizena |        10 |
| Tepung terigu  |        10 |
+----------------+-----------+

  3. Untuk mendapatkan data total jumlah yang di 'muat' berdasarkan tabel 'tbl_muat' untuk tanggal 1 s.d 3 Oktober 2013 bisa dibuatkan query[3] sebagai berikut: 

    SELECT 
 jenis,
 SUM(jumlah) AS jml_muat
FROM 
 tbl_muat 
WHERE 
 tgl BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY
 jenis;

    hasil yang didapat dari query itu adalah: 

    +----------------+------------+--------+
| jenis          | tgl        | jumlah |
+----------------+------------+--------+
| Tepung kanji   | 2013-10-02 |     10 |
| Tepung terigu  | 2013-10-02 |     10 |
| Tepung maizena | 2013-10-04 |      5 |
+----------------+------------+--------+

Menggunakan Join

Berdasarkan query yang dibuat sebelumnya, kita dapatkan jumlah masing-masing per-table-nya. Untuk menggabungkan hasil-hasil tersebut kita gunakan klausa JOIN pada query-nya. Kita umpamakan dari query [1] kita dapatkan tabel a, dari query [2] kita dapatkan tabel b dan dari query [3] kita peroleh tabel c; maka kita bisa gabungkan menjadi:

SELECT 
  a.jenis,
  a.jml_bongkar,
  b.jml_kemas,
  c.jml_muat
FROM 
  a
  JOIN b ON a.jenis=b.jenis
  JOIN c ON a.jenis=c.jenis

Dengan memasukan query-query sebelumnya ke dalam query tersebut (men-substitusi tabel a, b dan c dengan query sebelumnya) diperoleh query selengkapnya sebagai berikut: 

SELECT 
  a.jenis,
  a.jml_bongkar,
  b.jml_kemas,
  c.jml_muat
FROM 
  (SELECT 
     jenis,
     SUM(jumlah) AS jml_bongkar
    FROM 
     tbl_bongkar 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) a
  JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_kemas
    FROM 
     tbl_kemas 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) b ON a.jenis=b.jenis
  JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_muat
    FROM 
     tbl_muat 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) c ON a.jenis=c.jenis

Dari query yang diberikan tersebut kita dapatkan data sebagai berikut: 

+---------------+-------------+-----------+----------+
| jenis         | jml_bongkar | jml_kemas | jml_muat |
+---------------+-------------+-----------+----------+
| Tepung kanji  |          30 |        30 |       10 |
| Tepung terigu |          20 |        10 |       10 |
+---------------+-------------+-----------+----------+

Menyempurnakan Query

Dari query sebelumnya terlihat bahwa hasil yang diperoleh tidak mencantumkan data untuk jenis 'Tepung maizena', padahal dari query [1] dan query [2] diperoleh jumlah nilai untuk data jenis 'Tepung maizena'. Mengapa demikian? karena dengan hanya memakai klausa JOIN maka hanya ditampilkan data yang ada dikedua sisi saja. Dalam hal ini data untuk jenis 'Tepung maizena' tidak ditemukan pada query [3] sehingga dalam query joinnya tidak ditampilkan semuanya. Untuk mengatasi ini kita dapat gunakan klausa LEFT JOIN atau RIGHT JOIN. Sebagai contoh, kita pakai klausa LEFT JOIN sebagai berikut:

SELECT 
  a.jenis,
  a.jml_bongkar,
  b.jml_kemas,
  c.jml_muat
FROM 
  (SELECT 
     jenis,
     SUM(jumlah) AS jml_bongkar
    FROM 
     tbl_bongkar 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) a
  LEFT JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_kemas
    FROM 
     tbl_kemas 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) b ON a.jenis=b.jenis
  LEFT JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_muat
    FROM 
     tbl_muat 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) c ON a.jenis=c.jenis

Dengan klausa LEFT JOIN maka yang sebagai acuan adalah tabel yang disebelah kiri, jika ada nilai field pada tabel yang sebelah kiri sedangkan di sebelah kanan tidak ada maka akan tetap ditampilkan, dengan nilai field yang sebelah kanan berupa nilai NULL. Dari query diatas maka kita peroleh:
+----------------+-------------+-----------+----------+
| jenis          | jml_bongkar | jml_kemas | jml_muat |
+----------------+-------------+-----------+----------+
| Tepung kanji   |          30 |        30 | 10       |
| Tepung maizena |          65 |        10 | NULL     |
| Tepung terigu  |          20 |        10 | 10       |
+----------------+-------------+-----------+----------+

Kita dapat mengganti nilai 'NULL' dengan semisal nilai kosong (empty string) dengan mengunakan pengecekan kondisi memakai flow control operator IFNULL() seperti berikut:

SELECT 
  a.jenis,
  a.jml_bongkar,
  b.jml_kemas,
  IFNULL(c.jml_muat,'') AS jml_muat
FROM 
  (SELECT 
     jenis,
     SUM(jumlah) AS jml_bongkar
    FROM 
     tbl_bongkar 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) a
  LEFT JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_kemas
    FROM 
     tbl_kemas 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) b ON a.jenis=b.jenis
  LEFT JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_muat
    FROM 
     tbl_muat 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) c ON a.jenis=c.jenis

Sehingga diperoleh hasil akhir sebagai berikut:
+----------------+-------------+-----------+----------+
| jenis          | jml_bongkar | jml_kemas | jml_muat |
+----------------+-------------+-----------+----------+
| Tepung kanji   |          30 |        30 | 10       |
| Tepung maizena |          65 |        10 |          |
| Tepung terigu  |          20 |        10 | 10       |
+----------------+-------------+-----------+----------+

Kita dapat mengganti klausa ON a.jenis=b.jenis dan ON a.jenis=c.jenis menjadi cukup ditulis USING(jenis) karena kedua sisi memakai nama field yang sama sebagai relasinya. Sehingga query akhirnya dapat dituliskan sebagai:

SELECT 
  a.jenis,
  a.jml_bongkar,
  b.jml_kemas,
  IFNULL(c.jml_muat,'') AS jml_muat
FROM 
  (SELECT 
     jenis,
     SUM(jumlah) AS jml_bongkar
    FROM 
     tbl_bongkar 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) a
  LEFT JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_kemas
    FROM 
     tbl_kemas 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) b USING(jenis)
  LEFT JOIN 
   (SELECT 
     jenis,
     SUM(jumlah) AS jml_muat
    FROM 
     tbl_muat 
    WHERE 
     tgl BETWEEN '2013-10-01' AND '2013-10-03'
    GROUP BY
     jenis) c USING(jenis)

Artikel Terkait

Data Mining Series

Decision Support System Series

Expert System Series

General Articles

Islamic Series