Menghasilkan Daftar Master-detail dan Kesimpulan - MySQL Dasar #77

Dua buah tabel yang berelasi akan memiliki master-detail, dan pada kasus ini kita akan menghasilkan sebuah daftar yang menunjukan tiap rekaman master dengan rekaman – rekaman detailnya, atau sebuah daftar yang menyimpulkan rekaman – rekaman tiap master. Solusi terhadap permasalahan ini melibatkan operasi penggabungan tetapi tipe penggabungan bergantung pada pertanyaan yang akan dijawab. 

Lihat daftar isi : Menggunakan Multi Tabel - MySQL Dasar #72 

Untuk menghasilkan sebuah daftar yang hanya membuat rekaman – rekaman master untuk beberapa rekaman detail yang ada, kita bisa mengggunakan penggabungan biasa berdasarkan primary keu pada tabel master. Untuk menghasilkan sebuah daftar yang memuat masukan – masukan untuk semua rekaman master, Anda bisa menggunakan LEFT JOIN. 

MariaDB [root93]> SELECT seniman.nama, lukisan.judul FROM seniman, lukisan
    -> WHERE seniman.a_id=lukisan.a_id
    -> ORDER by 1,2;
+----------+-------------------+
| nama     | judul             |
+----------+-------------------+
| Da Vinci | The last super    |
| Da Vinci | The Mona Lisa     |
| Renoir   | Les Deux Soeurs   |
| Van Gogh | Stary Night       |
| Van Gogh | The Potato Eaters |
| Van Gogh | The Rocks         |
+----------+-------------------+
6 rows in set (0.011 sec)

MariaDB [root93]> SELECT seniman.nama, lukisan.judul FROM seniman
    -> LEFT JOIN lukisan ON seniman.a_id = lukisan.a_id
    -> ORDER BY 1,2;
+----------+-------------------+
| nama     | judul             |
+----------+-------------------+
| Da Vinci | The last super    |
| Da Vinci | The Mona Lisa     |
| Monet    | NULL              |
| Picasso  | NULL              |
| Renoir   | Les Deux Soeurs   |
| Van Gogh | Stary Night       |
| Van Gogh | The Potato Eaters |
| Van Gogh | The Rocks         |
+----------+-------------------+
8 rows in set (0.001 sec)

MariaDB [root93]> SELECT a_id, COUNT(a_id) AS jumlah
    -> FROM lukisan
    -> GROUP by a_id;
+------+--------+
| a_id | jumlah |
+------+--------+
|    1 |      2 |
|    3 |      3 |
|    5 |      1 |
+------+--------+
3 rows in set (0.001 sec)

Tentu keluaran tersebut tidak berarti kecuali jika Anda memiliki semua nomor id seniman. Untuk menampilkan seniman berdasarkan nama bukan berdasarkan id, Anda dapat melakukan penggabungan tabel lukisan dengan tabel seniman.

MariaDB [root93]> SELECT seniman.nama AS pelukis,
    -> COUNT(lukisan.a_id) AS jumlah
    -> FROM seniman, lukisan
    -> WHERE seniman.a_id=lukisan.a_id
    -> GROUP by seniman.nama;
+----------+--------+
| pelukis  | jumlah |
+----------+--------+
| Da Vinci |      2 |
| Renoir   |      1 |
| Van Gogh |      3 |
+----------+--------+
3 rows in set (0.001 sec)

Selanjutnya mungkin juga Anda akan bertanya, “Berapa banyak lukisan yang dilukis oleh tiap seniman ?”, dan pertanyaan ini tentunya merupakan pertanyaan yang sama seperti sebelumnya dan tentu kita akan menggunakan query yang sama untuk menajawabnya, sepanjang setiap seniman pada tabel seniman memilki satu rekaman pada tabel lukisan terkait. 

Tetapi jika misalnya kita memiliki seniman – seniman pada tabel seniman yang tidak direpresentasikan oleh sembarang lukisan pada koleksi, maka seniman – seniman tersebut tidak akan ditampilkan pada keluaran. Untuk menghasilkan kesimpulan jumlah lukisan per seniman yang mencantumkan bahkan seniman – seniman yang tidak memiliki lukisan, kita bisa menggunakan LEFT JOIN

MariaDB [root93]> SELECT seniman.nama AS pelukis, COUNT(lukisan.a_id) AS jumlah
    -> FROM seniman
    -> LEFT JOIN lukisan
    -> ON seniman.a_id=lukisan.a_id
    -> GROUP by seniman.nama;
+----------+--------+
| pelukis  | jumlah |
+----------+--------+
| Da Vinci |      2 |
| Monet    |      0 |
| Picasso  |      0 |
| Renoir   |      1 |
| Van Gogh |      3 |
+----------+--------+
5 rows in set (0.001 sec)

perlu diperhatikan bahwa count(*) dan count(ekspresi) akan menghasilkan nilai berbeda, ini sudah pernah dibahas sebelumnya pada query kesimpulan bahwa count(*) akan menghitung semua baris dan disini LEFT JOIN akan menghasilkan satu baris NULL ketika seniman tidak memilki data pada tabel lukisan yang tentunya akan menghasilkan nilai 1. 

LEFT JOIN cocok untuk tipe – tipe kesimpulan lain. Untuk menghasilkan kolom tambahan yang menunjukan nilai total dan nilai rerata dari jumlah lukisan untuk tiap seniman pada tabel seniman, Anda bisa menggunakan query ini :

MariaDB [root93]> SELECT seniman.nama AS pelukis,
    -> COUNT(lukisan.a_id) AS 'jumlah lukisan',
    -> SUM(lukisan.harga) AS 'total harga',
    -> AVG(lukisan.harga) AS 'rerata'
    -> FROM seniman LEFT JOIN lukisan
    -> ON seniman.a_id=lukisan.a_id
    -> GROUP by seniman.nama;
+----------+----------------+-------------+---------+
| pelukis  | jumlah lukisan | total harga | rerata  |
+----------+----------------+-------------+---------+
| Da Vinci |              2 |         121 | 60.5000 |
| Monet    |              0 |        NULL |    NULL |
| Picasso  |              0 |        NULL |    NULL |
| Renoir   |              1 |          64 | 64.0000 |
| Van Gogh |              3 |         148 | 49.3333 |
+----------+----------------+-------------+---------+
5 rows in set (0.001 sec)

Jika kita perhatikan bahwa fungsi COUNT() akan menghasilkan nilai 0 untuk setiap seniman yang tidak direpresentasikan tetapi SUM dan AVG menghasilkan NULL. Hal tersebut dikarenakan kedua fungsi itu dihitung sebagai produk dari apa yang dihasilkan COUNT. 

Jika yang dihasilkan COUNT() itu adalah nol, maka nilannya menjadi tidak terdefinisi. Sehingga kita bisa saja menambahkan IFNULL didalam fungsi agregat kesimpulannya seperti contoh berikut :

MariaDB [root93]> SELECT seniman.nama AS pelukis,
    -> COUNT(lukisan.a_id) AS 'jml lukisan',
    -> IFNULL(SUM(lukisan.harga),0) AS 'total harga',
    -> IFNULL(AVG(lukisan.harga),0) AS 'harga rerata'
    -> FROM seniman LEFT JOIN lukisan
    -> ON seniman.a_id=lukisan.a_id
    -> GROUP by seniman.nama;
+----------+-------------+-------------+--------------+
| pelukis  | jml lukisan | total harga | harga rerata |
+----------+-------------+-------------+--------------+
| Da Vinci |           2 |         121 |      60.5000 |
| Monet    |           0 |           0 |       0.0000 |
| Picasso  |           0 |           0 |       0.0000 |
| Renoir   |           1 |          64 |      64.0000 |
| Van Gogh |           3 |         148 |      49.3333 |
+----------+-------------+-------------+--------------+
5 rows in set (0.041 sec)