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.

Salah satu bentuk pertanyaan master detail adalah “Siapa seniman yang melukis tiap lukisan” dan ini merupakan penggabungan biasa yang mencocokan tiap rekaman lukisan dengan rekaman seniman terkait berdasarkan nilai – nilai ID seniman.

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)

Jenis penggabungan seperti itu sudah cukup untuk menjawa pertanyaan yang diajukan, sepanjang Anda hanya ingin menampilkan daftar rekaman – rekaman master yang memiliki rekaman- rekaman detail. Namun, bentuk lain dari pertanyaan master-detail adalah “Lukisan mana yang dilukis oleh tiap seniman ?”, pertanyaan ini mirip, tetapi tidak sama.

Pertanyaan ini akan memiliki jawaban yang berbeda jika ada seniman pada tabel seniman yang tidak direpresentasikan pada tabel lukisan sehingga pertanyaan ini memerlukan penyelesaian dengan query yang berbeda agar menghasilkan jawaban yang tepat.

Dalam kasus ini, keluaran penggabungan harus mencantumkan rekaman – rekaman pada satu tabel yang tidak ada kecocokan dengan tabel lain. Inilah bentuk dari permasalahan dalam ‘mencari rekaman-rekaman yang tak cocok.

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)

Baris yang menghasilkan nilai NULL merupakan representasi dari seniman yang tidak memiliki lukisan. Prinsip ini dapat diterapkan ketika misalnya kita ingin menghasilkan kesimpulan menggunakan tabel master dan tabel detail. Sebagai contoh, untuk menyimpulkan koleksi seni berdasarkan jumlah lukisan per seniman, Anda akan bertanya, “Berapa banyak lukisan yang dilukis tiap seniman pada tabel lukisan?” untuk mencari jawabannya berdasarkan id seniman, Anda dapat menghitung jumlah lukisan dengan menggunakan query ini :

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)

Ahmad Zaelani

Seorang laki - laki biasa yang senang duduk sendirian di pinggir pantai, pengagum embun pagi dan lembayung senja, suka blogging, coding dan jogging. Moto hidupnya adalah hidup santai dan simple

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

0 Response to "Menghasilkan Daftar Master-detail dan Kesimpulan - MySQL Dasar #77"

Post a Comment