union-mysql.txt

D:\xampp\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> -- create table1
mysql> -- -----------------------------------------------

mysql> create table my_table1 as
    -> select '089111' no_tlpn, 'Agus' nama, 100 jumlah, '00:10' durasi, 'Voice' tipe union all
    -> select '089111' no_tlpn, 'Agus' nama, 200 jumlah, '00:20' durasi, 'Voice' tipe union all
    -> select '089111' no_tlpn, 'Agus' nama, 500 jumlah, '00:10' durasi, 'Data Roaming' tipe union all
    -> select '089112' no_tlpn, 'Riki' nama, 100 jumlah, '00:10' durasi, 'Voice' tipe union all
    -> select '089112' no_tlpn, 'Riki' nama, 100 jumlah, '00:32' durasi, 'Data Roaming' tipe ;
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> -- periksa apakah table1 berhasil dibuat
mysql> -- -----------------------------------------------

mysql> select * from my_table1;
+---------+------+--------+--------+--------------+
| no_tlpn | nama | jumlah | durasi | tipe         |
+---------+------+--------+--------+--------------+
| 089111  | Agus |    100 | 00:10  | Voice        |
| 089111  | Agus |    200 | 00:20  | Voice        |
| 089111  | Agus |    500 | 00:10  | Data Roaming |
| 089112  | Riki |    100 | 00:10  | Voice        |
| 089112  | Riki |    100 | 00:32  | Data Roaming |
+---------+------+--------+--------+--------------+
5 rows in set (0.00 sec)

mysql> -- create table2
mysql> -- -----------------------------------------------

mysql> create table my_table2 as
    -> select '089111' no_tlpn, 20000 abonemen, 2000 ppn, -3500 discount union all
    -> select '089112' no_tlpn, 20000 abonemen, 2000 ppn, -3500 discount ;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> -- periksa apakah table2 berhasil dibuat
mysql> -- -----------------------------------------------

mysql> select * from my_table2;
+---------+----------+------+----------+
| no_tlpn | abonemen | ppn  | discount |
+---------+----------+------+----------+
| 089111  |    20000 | 2000 |    -3500 |
| 089112  |    20000 | 2000 |    -3500 |
+---------+----------+------+----------+
2 rows in set (0.00 sec)

mysql> -- kalau kita perhatikan, hasil akhir yang diinginkan
mysql> -- adalah gabungan dari tabel1 dan tabel2 yang dipecah
mysql> -- menjadi row tersendiri untuk abonemen, ppn dan discount
mysql> -- mari kita ambil data abonemen dari table2
mysql> -- pastikan bahwa struktur field hasil query sama dengan table1
mysql> -- ------------------------------------------------------------

mysql> select a.no_tlpn, a.nama, b.abonemen, null durasi, 'Abonemen' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn ;
+---------+------+----------+--------+----------+
| no_tlpn | nama | abonemen | durasi | tipe     |
+---------+------+----------+--------+----------+
| 089111  | Agus |    20000 |   NULL | Abonemen |
| 089111  | Agus |    20000 |   NULL | Abonemen |
| 089111  | Agus |    20000 |   NULL | Abonemen |
| 089112  | Riki |    20000 |   NULL | Abonemen |
| 089112  | Riki |    20000 |   NULL | Abonemen |
+---------+------+----------+--------+----------+
5 rows in set (0.00 sec)

mysql> -- ups... hasil query banyak yang duplikat
mysql> -- tambahkan distinct untuk mendapatkan hasil unique
mysql> -- ------------------------------------------------------------

mysql> select distinct a.no_tlpn, a.nama, b.abonemen, null durasi, 'Abonemen' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn ;
+---------+------+----------+--------+----------+
| no_tlpn | nama | abonemen | durasi | tipe     |
+---------+------+----------+--------+----------+
| 089111  | Agus |    20000 |   NULL | Abonemen |
| 089112  | Riki |    20000 |   NULL | Abonemen |
+---------+------+----------+--------+----------+
2 rows in set (0.00 sec)

mysql> -- kemudian kita ambil data ppn dari table2
mysql> -- pastikan bahwa struktur field hasil query sama dengan table1
mysql> -- ------------------------------------------------------------

mysql> select distinct a.no_tlpn, a.nama, b.ppn, null durasi, 'PPN' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn ;
+---------+------+------+--------+------+
| no_tlpn | nama | ppn  | durasi | tipe |
+---------+------+------+--------+------+
| 089111  | Agus | 2000 |   NULL | PPN  |
| 089112  | Riki | 2000 |   NULL | PPN  |
+---------+------+------+--------+------+
2 rows in set (0.00 sec)

mysql> -- mari kita ambil data discount dari table2
mysql> -- pastikan bahwa struktur field hasil query sama dengan table1
mysql> -- ------------------------------------------------------------

mysql> select distinct a.no_tlpn, a.nama, b.discount, null durasi, 'Discount' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn ;
+---------+------+----------+--------+----------+
| no_tlpn | nama | discount | durasi | tipe     |
+---------+------+----------+--------+----------+
| 089111  | Agus |    -3500 |   NULL | Discount |
| 089112  | Riki |    -3500 |   NULL | Discount |
+---------+------+----------+--------+----------+
2 rows in set (0.00 sec)

mysql> -- sekarang gabungkan semuanya menggunakan UNION ALL
mysql> -- ------------------------------------------------------------

mysql> select * from my_table1
    ->
    -> union all
    -> select distinct a.no_tlpn, a.nama, b.abonemen, null durasi, 'Abonemen' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn
    ->
    -> union all
    -> select distinct a.no_tlpn, a.nama, b.ppn, null durasi, 'PPN' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn
    ->
    -> union all
    -> select distinct a.no_tlpn, a.nama, b.discount, null durasi, 'Discount' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn ;
+---------+------+--------+--------+--------------+
| no_tlpn | nama | jumlah | durasi | tipe         |
+---------+------+--------+--------+--------------+
| 089111  | Agus |    100 | 00:10  | Voice        |
| 089111  | Agus |    200 | 00:20  | Voice        |
| 089111  | Agus |    500 | 00:10  | Data Roaming |
| 089112  | Riki |    100 | 00:10  | Voice        |
| 089112  | Riki |    100 | 00:32  | Data Roaming |
| 089111  | Agus |  20000 | NULL   | Abonemen     |
| 089112  | Riki |  20000 | NULL   | Abonemen     |
| 089111  | Agus |   2000 | NULL   | PPN          |
| 089112  | Riki |   2000 | NULL   | PPN          |
| 089111  | Agus |  -3500 | NULL   | Discount     |
| 089112  | Riki |  -3500 | NULL   | Discount     |
+---------+------+--------+--------+--------------+
11 rows in set (0.00 sec)

mysql> -- ups... hasilnya tidak terurut berdasarkan nomor telepon
mysql> -- tambahkan cluase ORDER BY di akhir query
mysql> -- ------------------------------------------------------------

mysql> select * from my_table1
    ->
    -> union all
    -> select distinct a.no_tlpn, a.nama, b.abonemen, null durasi, 'Abonemen' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn
    ->
    -> union all
    -> select distinct a.no_tlpn, a.nama, b.ppn, null durasi, 'PPN' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn
    ->
    -> union all
    -> select distinct a.no_tlpn, a.nama, b.discount, null durasi, 'Discount' tipe
    -> from my_table1 a join my_table2 b on a.no_tlpn = b.no_tlpn
    ->
    -> order by no_tlpn, nama ;
+---------+------+--------+--------+--------------+
| no_tlpn | nama | jumlah | durasi | tipe         |
+---------+------+--------+--------+--------------+
| 089111  | Agus |    200 | 00:20  | Voice        |
| 089111  | Agus |   2000 | NULL   | PPN          |
| 089111  | Agus |    500 | 00:10  | Data Roaming |
| 089111  | Agus |  -3500 | NULL   | Discount     |
| 089111  | Agus |  20000 | NULL   | Abonemen     |
| 089111  | Agus |    100 | 00:10  | Voice        |
| 089112  | Riki |   2000 | NULL   | PPN          |
| 089112  | Riki |    100 | 00:10  | Voice        |
| 089112  | Riki |    100 | 00:32  | Data Roaming |
| 089112  | Riki |  -3500 | NULL   | Discount     |
| 089112  | Riki |  20000 | NULL   | Abonemen     |
+---------+------+--------+--------+--------------+
11 rows in set (0.00 sec)

mysql>