Menggunakan kueri gabungan untuk menggabungkan beberapa kueri ke dalam satu hasil

Dalam contoh dari bagian sebelumnya yang menggunakan database Northwind, hanya data dari dua tabel yang digabungkan. Namun, Anda dapat menggabungkan tiga atau lebih tabel dalam kueri gabungan dengan mudah. Misalnya, jika menggunakan contoh sebelumnya, Anda juga dapat menyertakan nama karyawan dalam output kueri. Anda dapat menyelesaikan tugas tersebut dengan menambahkan kueri ketiga dan menggabungkannya dengan pernyataan SQL sebelumnya dan kata kunci UNION tambahan seperti ini:

SELECT Customers.Company, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT Suppliers.Company, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT Employees.Company, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Ketika melihat hasil dalam tampilan lembar data, semua karyawan akan tercantum dengan contoh nama perusahaan, yang mungkin tidak begitu berguna. Jika ingin bidang itu menunjukkan apakah seseorang adalah karyawan kantor, dari pemasok, atau dari pelanggan, Anda dapat menyertakan nilai tetap sebagai alternatif untuk nama perusahaan. Berikut tampilan SQL-nya:

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers

UNION

SELECT "Supplier" As Employment, Suppliers.[Last Name], Suppliers.[First Name]
FROM Suppliers

UNION

SELECT "In-house" As Employment, Employees.[Last Name], Employees.[First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Berikut tampilan hasil yang akan muncul dalam tampilan lembar data. Access menampilkan kelima contoh data ini:

Kueri di atas dapat lebih dikurangi karena Access hanya membaca nama bidang output dari kueri pertama dalam kueri gabungan. Di sini, kami telah menghapus output dari bagian kueri kedua dan ketiga:

SELECT "Customer" As Employment, [Last Name], [First Name]
FROM Customers

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers

UNION

SELECT "In-house", [Last Name], [First Name]
FROM Employees

ORDER BY [Last Name], [First Name];

Dalam sebuah kueri gabungan Access, pengurutan hanya diperbolehkan sekali, tetapi setiap kueri dapat difilter satu per satu. Dengan menggunakan kueri gabungan bagian sebelumnya, berikut contoh pemfilteran setiap kueri dengan menambahkan klausul WHERE.

SELECT "Customer" As Employment, Customers.[Last Name], Customers.[First Name]
FROM Customers
WHERE [State/Province] = "UT"

UNION

SELECT "Supplier", [Last Name], [First Name]
FROM Suppliers
WHERE [Job Title] = "Sales Manager"

UNION

SELECT "In-house", Employees.[Last Name], Employees.[First Name]
FROM Employees
WHERE City = "Seattle"

ORDER BY [Last Name], [First Name];

Beralihlah ke tampilan lembar data dan Anda akan melihat hasil yang mirip dengan ini:

Jika kueri yang digabungkan sangat berbeda, bidang output harus menggabungkan data dari tipe yang berbeda. Jika demikian, kueri gabungan seringkali hanya mengembalikan hasil sebagai tipe data teks karena tipe data tersebut dapat berisi teks dan angka.

Untuk memahami cara kerjanya, kami akan menggunakan kueri gabungan Transaksi Produk dalam contoh database Northwind. Buka contoh database, lalu buka kueri Transaksi Produk dalam tampilan lembar data. Sepuluh data terakhir seharusnya mirip dengan output ini:

Anggap saja Anda ingin bidang Jumlah dipisahkan menjadi dua, yaitu Beli dan Jual. Anda juga ingin mengisi nilai nol tetap untuk bidang tanpa nilai. Berikut tampilan SQL untuk kueri gabungan ini:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], 0 As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, 0 As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC; 

Jika beralih ke tampilan lembar data, Anda akan melihat sepuluh data terakhir ditampilkan seperti berikut:

Masih dengan contoh ini, bagaimana jika Anda ingin bidang dengan nilai nol menjadi kosong? Anda dapat mengubah SQL agar tidak menampilkan apa pun sebagai ganti nilai nol dengan menambahkan kata kunci Null seperti berikut:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Namun, seperti yang dapat dilihat jika beralih ke tampilan lembar data, kini Anda memiliki hasil yang tidak terduga. Dalam kolom Beli, setiap bidang kosong:

Hal ini terjadi karena Access menentukan tipe data bidang dari kueri pertama. Dalam contoh ini, Null bukanlah angka.

Oleh karena itu, apa yang terjadi jika Anda mencoba dan menyisipkan string kosong untuk nilai bidang yang kosong? SQL untuk percobaan ini dapat terlihat seperti ini:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], "" As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, "" As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Saat beralih ke tampilan lembar data, Anda akan melihat bahwa Access mengambil nilai Beli, tetapi telah mengonversi nilai menjadi teks. Anda bisa mengetahui ini adalah nilai teks karena nilai teks diratakan kiri dalam tampilan lembar data. String kosong dalam kueri pertama bukanlah angka yang mengapa Anda melihat hasil ini. Anda juga akan melihat bahwa nilai Jual juga dikonversi menjadi teks karena rekaman pembelian berisi string kosong.

Lalu, bagaimana cara memecahkan teka-teki ini?

Solusinya adalah memaksa kueri untuk menganggap nilai bidang menjadi angka. Hal ini dapat dilakukan dengan ekspresi:

IIf(False, 0, Null)

Syarat untuk diperiksa, False, tidak akan menjadi True, maka ekspresi tersebut akan selalu mengembalikan Null, tetapi Access masih mengevaluasi kedua opsi output dan menentukan output menjadi angka atau Null.

Berikut cara menggunakan ekspresi ini dalam contoh yang dapat dimodifikasi:

SELECT [Product ID], [Order Date], [Company Name], [Transaction], IIf(False, 0, Null) As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Perlu diingat bahwa mengubah kueri kedua tidak harus dilakukan.

Jika beralih ke tampilan lembar data, Anda akan melihat hasil yang diinginkan:

Metode alternatif untuk mendapatkan hasil yang sama adalah menambahkan kueri dalam kueri gabungan dengan kueri lainnya pada bagian awal:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

Untuk setiap bidang, Access mengembalikan nilai tetap dari tipe data yang ditentukan. Tentu saja, Anda tidak ingin output kueri ini mengganggu hasilnya, sehingga Anda perlu menyertakan klausul WHERE ke False:

WHERE False

Ini trik sederhana karena selalu bersifat false dan kueri tidak mengembalikan apa pun. Gabungkan pernyataan ini dengan SQL yang ada dan pernyataan lengkap telah berhasil dibuat, yaitu:

SELECT 
    0 As [Product ID], Date() As [Order Date], 
    "" As [Company Name], "" As [Transaction], 
    0 As Buy, 0 As Sell
FROM [Product Orders]
WHERE False

UNION

SELECT [Product ID], [Order Date], [Company Name], [Transaction], Null As Buy, [Quantity] As Sell
FROM [Product Orders]

UNION

SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity] As Buy, Null As Sell
FROM [Product Purchases]

ORDER BY [Order Date] DESC;

Kasus khusus untuk kueri gabungan adalah menggabungkan serangkaian data dengan sebuah data yang berisi jumlah dari satu bidang atau lebih.

Berikut contoh lain yang dapat Anda buat dalam contoh database Northwind untuk menunjukkan cara mendapatkan total dalam kueri gabungan.

1. Buat kueri sederhana baru untuk menampilkan pembelian bir (ID Produk=34 dalam database Northwind) menggunakan sintaks SQL berikut:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

2. Beralihlah ke tampilan lembar data, dan Anda akan melihat empat pembelian:

   Tanggal Diterima	Jumlah
   22/1/2006	100
   22/1/2006	60
   4/4/2006	50
   5/4/2006	300

3. Untuk mendapatkan total, buat kueri agregasi sederhana menggunakan SQL berikut:

   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))

4. Beralihlah ke tampilan lembar data, dan Anda akan melihat satu data saja:

   MaksTanggal Diterima	TotalJumlah
   5/4/2006	510

5. Gabungkan kedua kueri ini ke dalam kueri gabungan untuk menambahkan data dengan total kuantitas ke data pembelian:

   SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   UNION
   
   SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
   FROM [Purchase Order Details]
   WHERE ((([Purchase Order Details].[Product ID])=34))
   
   ORDER BY [Purchase Order Details].[Date Received];

6. Beralihlah ke tampilan lembar data, dan Anda akan melihat empat pembelian dengan jumlah masing-masing, diikuti dengan data yang menjumlahkan kuantitas:

   Tanggal Diterima	Jumlah
   22/1/2006	60
   22/1/2006	100
   4/4/2006	50
   5/4/2006	300
   5/4/2006	510

Penjelasan di atas mencakup dasar-dasar menambahkan total ke kueri gabungan. Anda mungkin juga ingin menyertakan nilai tetap dalam kedua kueri seperti “Detail” dan “Total” untuk memisahkan total data dari data lainnya secara visual. Anda dapat meninjau penggunaan nilai tetap dalam bagian Menggabungkan tiga atau lebih tabel dan kueri dalam kueri gabungan.

Kueri gabungan di Access secara default hanya menyertakan data yang berbeda. Namun, bagaimana jika Anda ingin menyertakan semua data? Contoh lain mungkin berguna di sini.

Dalam bagian sebelumnya, kami menunjukkan cara membuat total dalam kueri gabungan. Ubah SQL kueri gabungan tersebut untuk menyertakan ID Produk= 48:

SELECT [Purchase Order Details].[Date Received], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION

SELECT Max([Date Received]), Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Purchase Order Details].[Date Received];

Beralihlah ke tampilan lembar data, dan Anda akan melihat hasil yang cenderung kurang tepat:

Tentu saja, satu data tidak mengembalikan dua kali jumlah secara keseluruhan.

Hal tersebut terjadi karena pada satu hari, jumlah cokelat yang sama terjual dua kali, seperti yang dicatat dalam tabel Detail Pesanan Pembelian. Berikut hasil kueri pemilihan sederhana yang menampilkan kedua data dalam contoh database Northwind:

Dalam kueri gabungan yang disebutkan sebelumnya, Anda dapat melihat bahwa bidang ID Pesanan Pembelian tidak disertakan dan bahwa dua bidang tersebut tidak terdiri dari dua catatan yang berbeda.

Jika ingin menyertakan semua catatan, gunakan UNION ALL sebagai ganti UNION dalam SQL Anda. Kemungkinan besar ini akan berdampak pada pengurutan hasil, sehingga Anda perlu menyertakan klausul ORDER BY untuk menentukan urutan pengurutan. Berikut SQL yang diubah dari contoh sebelumnya:

SELECT [Purchase Order Details].[Date Received], Null As [Total], [Purchase Order Details].Quantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

UNION ALL

SELECT Max([Date Received]), "Total" As [Total], Sum([Quantity]) AS SumOfQuantity
FROM [Purchase Order Details]
WHERE ((([Purchase Order Details].[Product ID])=48))

ORDER BY [Total];

Beralihlah ke tampilan lembar data, dan selain total, Anda akan melihat semua detail sebagai data terakhir:

Kueri gabungan sering digunakan sebagai sumber data untuk kontrol kotak kombo pada formulir. Anda dapat menggunakan kotak kombo tersebut untuk memilih nilai guna memfilter data formulir. Misalnya, memfilter data karyawan menurut kota mereka.

Guna mengetahui cara kerjanya, berikut contoh lain yang dapat Anda buat dalam contoh database Northwind untuk menjelaskan skenario ini.

1. Buat kueri pemilihan sederhana menggunakan sintaks SQL ini:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees;

2. Beralihlah ke tampilan lembar data, dan Anda akan melihat hasil berikut:

   Kota	Filter
   Semarang	Semarang
   Bandung	Bandung
   Rembang	Rembang
   Kediri	Kediri
   Semarang	Semarang
   Rembang	Rembang
   Semarang	Semarang
   Rembang	Rembang
   Semarang	Semarang

3. Saat mengamati hasil tersebut, Anda mungkin tidak melihat banyak nilai. Perluas kueri dan ubah menjadi kueri gabungan dengan menggunakan SQL berikut:

   SELECT Employees.City, Employees.City AS Filter
   FROM Employees
   
   UNION
   
   SELECT "<All>", "*" AS Filter
   FROM Employees
   
   ORDER BY City;

4. Beralihlah ke tampilan lembar data, dan Anda akan melihat hasil berikut:

   Kota	Filter
   <Semua>	*
   Bandung	Bandung
   Kediri	Kediri
   Rembang	Rembang
   Semarang	Semarang

   Access melakukan penggabungan sembilan catatan yang sebelumnya ditampilkan dengan nilai bidang tetap, yaitu <All> dan "*".

   Karena klausul penggabungan ini tidak berisi UNION ALL, Access hanya mengembalikan data yang berbeda. Artinya, setiap kota dikembalikan sekali saja dengan nilai tetap yang identik.

5. Setelah memiliki kueri gabungan lengkap yang menampilkan nama kota sekali saja beserta opsi yang memilih semua kota dengan efektif, Anda dapat menggunakan kueri ini sebagai sumber data untuk kotak kombo pada formulir. Dengan menggunakan contoh ini sebagai model, Anda dapat membuat kontrol kotak kombo pada formulir, mengatur kueri ini sebagai sumber datanya, mengatur properti Lebar Kolom dari kolom Filter menjadi 0 (nol) untuk menyembunyikannya secara visual, lalu mengatur properti Kolom Terikat menjadi 1 untuk menunjukkan indeks kolom kedua. Dalam properti Filter dari formulir itu sendiri, Anda dapat menambahkan kode seperti berikut untuk mengaktifkan filter formulir menggunakan nilai yang dipilih dalam kontrol kotak kombo:

   Me.Filter = "[City] Like '" & Me![FilterComboBoxName].Value & "'"
   Me.FilterOn = True

   Pengguna formulir kemudian dapat memfilter data formulir untuk nama kota tertentu atau memilih <Semua> guna menampilkan semua data untuk semua kota.

Atas Halaman