Skip to content

Instantly share code, notes, and snippets.

@mishbah
Forked from hidayat365/union-mysql.txt
Created November 10, 2013 09:48
Show Gist options
  • Save mishbah/7396151 to your computer and use it in GitHub Desktop.
Save mishbah/7396151 to your computer and use it in GitHub Desktop.
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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment