Mencari Kecocokan Baris pada Dua Tabel - MySQL Dasar #74

Untuk dapat mencari kecocokan baris dari sebuah tabel maka kita bisa menggunakan baris – baris pada satu tabel untuk mencari kecocokan baris pada tabel lain, dimana tentunya disini kita akan menggunakan operasi penggabungan dengan tambahan paramter/klausa WHERE dalam upaya mencari kecocokan nilai pada dua buah tabel.

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

Sebelumnya terlebih dahulu disini kita akan membuat dua buah tabel sebagai bahan latihan yaitu tabel seniman dan tabel lukisan

MariaDB [root93]> CREATE TABLE seniman 
    -> (
    -> a_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(a_id),
    -> nama VARCHAR(65) NOT NULL,
    -> UNIQUE(nama)
    -> );
Query OK, 0 rows affected (0.270 sec)

MariaDB [root93]> DESC seniman;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| a_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| nama  | varchar(65)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.025 sec)

MariaDB [root93]> INSERT INTO seniman(nama) VALUES
    -> ('Da Vinci'),('Monet'),('Van Gogh'),
    -> ('Picasso'),('Renoir');
Query OK, 5 rows affected (0.095 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [root93]> CREATE TABLE lukisan
    -> (
    -> a_id INT UNSIGNED NOT NULL,
    -> p_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> judul VARCHAR(100) NOT NULL,
    -> negara VARCHAR(20) NOT NULL,
    -> harga INT UNSIGNED,
    -> INDEX (a_id),
    -> PRIMARY KEY(p_id)
    -> );
Query OK, 0 rows affected (0.248 sec)

MariaDB [root93]> DESC lukisan;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| a_id   | int(10) unsigned | NO   | MUL | NULL    |                |
| p_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| judul  | varchar(100)     | NO   |     | NULL    |                |
| negara | varchar(20)      | NO   |     | NULL    |                |
| harga  | int(10) unsigned | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)

MariaDB [root93]> INSERT INTO lukisan(a_id, judul, negara,harga)
    -> VALUES(1,'The last super','Amerika',34);
Query OK, 1 row affected (0.028 sec)

MariaDB [root93]> INSERT INTO lukisan(a_id, judul, negara,harga) VALUES
    -> (1,'The Mona Lisa','Spanyol',87),
    -> (3,'Stary Night','Inggris',48),
    -> (3,'The Potato Eaters','Jerman',67),
    -> (3,'The Rocks','Polandia',33),
    -> (5,'Les Deux Soeurs','Perancis',64);
Query OK, 5 rows affected (0.054 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [root93]> SELECT * FROM lukisan
    -> ORDER by a_id, p_id;
+------+------+-------------------+----------+-------+
| a_id | p_id | judul             | negara   | harga |
+------+------+-------------------+----------+-------+
|    1 |    1 | The last super    | Amerika  |    34 |
|    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    3 |    3 | Stary Night       | Inggris  |    48 |
|    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    3 |    5 | The Rocks         | Polandia |    33 |
|    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
+------+------+-------------------+----------+-------+
6 rows in set (0.001 sec)

Sampai disini kita sudah memiliki dua buah tabel yaitu tabel seniman dan lukisan. Kemudian jika misalnya disini Anda mencoba melakukan penggabungan secara utuh dari kedua buah tabel diatas tanpa menambahkan klausa WHERE maka hal tersebut tidak akan menghasilkan informasi, hanya sebatas menampilkan data saja

MariaDB [root93]> SELECT * FROM seniman, lukisan;
+------+----------+------+------+-------------------+----------+-------+
| a_id | nama     | a_id | p_id | judul             | negara   | harga |
+------+----------+------+------+-------------------+----------+-------+
|    1 | Da Vinci |    1 |    1 | The last super    | Amerika  |    34 |
|    2 | Monet    |    1 |    1 | The last super    | Amerika  |    34 |
|    4 | Picasso  |    1 |    1 | The last super    | Amerika  |    34 |
|    5 | Renoir   |    1 |    1 | The last super    | Amerika  |    34 |
|    3 | Van Gogh |    1 |    1 | The last super    | Amerika  |    34 |
|    1 | Da Vinci |    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    2 | Monet    |    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    4 | Picasso  |    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    5 | Renoir   |    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    3 | Van Gogh |    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    1 | Da Vinci |    3 |    3 | Stary Night       | Inggris  |    48 |
|    2 | Monet    |    3 |    3 | Stary Night       | Inggris  |    48 |
|    4 | Picasso  |    3 |    3 | Stary Night       | Inggris  |    48 |
|    5 | Renoir   |    3 |    3 | Stary Night       | Inggris  |    48 |
|    3 | Van Gogh |    3 |    3 | Stary Night       | Inggris  |    48 |
|    1 | Da Vinci |    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    2 | Monet    |    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    4 | Picasso  |    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    5 | Renoir   |    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    3 | Van Gogh |    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    1 | Da Vinci |    3 |    5 | The Rocks         | Polandia |    33 |
|    2 | Monet    |    3 |    5 | The Rocks         | Polandia |    33 |
|    4 | Picasso  |    3 |    5 | The Rocks         | Polandia |    33 |
|    5 | Renoir   |    3 |    5 | The Rocks         | Polandia |    33 |
|    3 | Van Gogh |    3 |    5 | The Rocks         | Polandia |    33 |
|    1 | Da Vinci |    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
|    2 | Monet    |    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
|    4 | Picasso  |    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
|    5 | Renoir   |    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
|    3 | Van Gogh |    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
+------+----------+------+------+-------------------+----------+-------+
30 rows in set (0.001 sec)

Jika kita menginginkan agar dilakukan pencocokan misalnya tiap seniman beserta daftar lukisannya maka kita perlu menambahkan klausa WHERE didalam query seperti berikut ini :

MariaDB [root93]> SELECT * FROM seniman, lukisan
    -> WHERE seniman.a_id=lukisan.a_id;
+------+----------+------+------+-------------------+----------+-------+
| a_id | nama     | a_id | p_id | judul             | negara   | harga |
+------+----------+------+------+-------------------+----------+-------+
|    1 | Da Vinci |    1 |    1 | The last super    | Amerika  |    34 |
|    1 | Da Vinci |    1 |    2 | The Mona Lisa     | Spanyol  |    87 |
|    3 | Van Gogh |    3 |    3 | Stary Night       | Inggris  |    48 |
|    3 | Van Gogh |    3 |    4 | The Potato Eaters | Jerman   |    67 |
|    3 | Van Gogh |    3 |    5 | The Rocks         | Polandia |    33 |
|    5 | Renoir   |    5 |    6 | Les Deux Soeurs   | Perancis |    64 |
+------+----------+------+------+-------------------+----------+-------+

Didalam klausa WHERE terdapat parameter a_id yang digunakan untuk memperjelas nilai yang akan dibandingkan hanya query yang dihasilkan terlihat berlebihan karena menampilkan 2 kolom a_id yang sama. Mungkin kita menginginkan agar kolom a_id ditampilkan sekali saja atau tidak perlu ditampilkan sama sekali. Untuk dapat memenuhi hal tersebut, maka kita perlu mendefinisikan nilai keleuaran didalam select seperti berikut :

MariaDB [root93]> SELECT seniman.nama, lukisan.judul,
    -> lukisan.negara, lukisan.harga
    -> FROM seniman, lukisan
    -> WHERE seniman.a_id = lukisan.a_id;
+----------+-------------------+----------+-------+
| nama     | judul             | negara   | harga |
+----------+-------------------+----------+-------+
| Da Vinci | The last super    | Amerika  |    34 |
| Da Vinci | The Mona Lisa     | Spanyol  |    87 |
| Van Gogh | Stary Night       | Inggris  |    48 |
| Van Gogh | The Potato Eaters | Jerman   |    67 |
| Van Gogh | The Rocks         | Polandia |    33 |
| Renoir   | Les Deux Soeurs   | Perancis |    64 |
+----------+-------------------+----------+-------+
6 rows in set (0.001 sec)

Dengan penambahan klausa WHERE ketika melakukan pencocokan, kita bisa menampilkan data secara lebih spesifik misalnya seperti berikut :

MariaDB [root93]> SELECT seniman.nama, lukisan.judul
    -> FROM seniman, lukisan
    -> WHERE seniman.nama='Van Gogh' AND seniman.a_id=lukisan.a_id;
+----------+-------------------+
| nama     | judul             |
+----------+-------------------+
| Van Gogh | Stary Night       |
| Van Gogh | The Potato Eaters |
| Van Gogh | The Rocks         |
+----------+-------------------+

atau contoh lainnya Anda ingin menemukan lukisan tertentu yang misalnya dibeli di Amerika dan Perancis, hal ini tentu saja sangat mudah, kita hanya perlu mendefinisikannya didalam klausa WHERE

MariaDB [root93]> SELECT DISTINCT seniman.nama
    -> FROM seniman, lukisan
    -> WHERE lukisan.negara IN('Amerika','Perancis') AND
    -> seniman.a_id=lukisan.a_id;
+----------+
| nama     |
+----------+
| Da Vinci |
| Renoir   |
+----------+
2 rows in set (0.001 sec)

Contoh lainnya bahwa penggabungan juga dapat diterapkan dengan fungsi – funsgi agregat untuk menghasilkan kesimpulan seperti berikut :

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

Anda bisa menambahkan fungsi agregat didalam penggabungan dengan query yang lebih kompleks, misalnya Anda ingin menampilkan berapa harga yang dibayarkan untuk tiap seniman dalam total atau nilai rata – rata

MariaDB [root93]> SELECT seniman.nama,
    -> COUNT(*) AS 'jumlah lukisan',
    -> SUM(lukisan.harga) AS 'total  harga',
    -> AVG(lukisan.harga) AS 'rata - rata'
    -> FROM seniman, lukisan
    -> WHERE seniman.a_id=lukisan.a_id
    -> GROUP by seniman.nama;
+----------+----------------+--------------+-------------+
| nama     | jumlah lukisan | total  harga | rata - rata |
+----------+----------------+--------------+-------------+
| Da Vinci |              2 |          121 |     60.5000 |
| Renoir   |              1 |           64 |     64.0000 |
| Van Gogh |              3 |          148 |     49.3333 |
+----------+----------------+--------------+-------------+
3 rows in set (0.001 sec)

Contoh lainnya, misalnya Anda bisa menghasilkan kesimpulan untuk menampilkan nama pelukis ‘Van Gogh’ beserta daftar lukisannya menggunakan GROUP_CONCAT dan GROUP by seperti berikut :

MariaDB [root93]> SELECT seniman.nama, GROUP_CONCAT(DISTINCT(lukisan.judul)) AS judul_lukisan
    -> FROM seniman, lukisan
    -> WHERE seniman.nama='Van Gogh' AND seniman.a_id=lukisan.a_id;
+----------+-----------------------------------------+
| nama     | judul_lukisan                           |
+----------+-----------------------------------------+
| Van Gogh | Stary Night,The Potato Eaters,The Rocks |
+----------+-----------------------------------------+
1 row in set (0.001 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

Mencari Kecocokan Baris pada Dua Tabel - MySQL Dasar #74

0 Response to "Mencari Kecocokan Baris pada Dua Tabel - MySQL Dasar #74"

Post a Comment