Hallo! Selamat Datang di Marketplace produk digital, Freelancer terbaik di indonesia (69167 Members, 793 Products, 4716 Topic, 13114 Comments)

Query Full Outer Join di Mysql

Dibuat
Login Terakhir 1 Tahun lalu,
Telah Dilihat 1898 Kali
Ahmad fatony budiman Mengatakan : Sultan Account 
  1. Sore All,
  2.  
  3. Ane mo tanya query full outer di mysql
  4.  
  5. kalo di sql server kan bisa
  6.  
  7. berikut query ane di mssql server
  8.  
  9. select a.sku1 as sku1inbound,sum(a.total) as qtyreceived,b.sku1 as sku1outbound,sum(b.total) as shipqty,
  10. SUM(a.total-b.total) as Selisih
  11. from hasilinbound_ari a
  12. full outer join hasiloutbound_ari b on a.sku1=b.sku1
  13. group by b.sku1,a.sku1
  14.  
  15. bagaimana caranya kalo di mysql
  16.  
  17. ane udah ganti pakai union tapi error,
  18.  
  19. mungkin ada yang bis a bantu..
  20.  
  21. makasih all

Ada 2 Jawaban

PHPMU.COM Support
commented on 15 Mei 2017 21:12:13		 
  1. Assalam, coba share sekalian dengan table database nya mas, biar bisa teman2 disini bantu cobakan juga,..
  2. dan sekalian share hasil yang di inginkan seperti apa-nya,..
Ahmad fatony budiman
commented on 17 Mei 2017 11:06:32		 
  1. Ane coba yang paling sederhana
  2.  
  3. berikut databasenya
  4.  
  5. Tabel test1
  6.  	id 	kodebarang 	total
  7. 	Edit 	Delete 	1 	15060001001 	1
  8. 	Edit 	Delete 	2 	15060001001 	1
  9. 	Edit 	Delete 	3 	15221135001 	1
  10. 	Edit 	Delete 	4 	22000007001 	2
  11.  
  12.  
  13. Tabel Test2
  14.  	id 	kodebarang 	total2
  15. 	Edit 	Delete 	1 	15060001001 	1
  16. 	Edit 	Delete 	2 	15060001001 	1
  17. 	Edit 	Delete 	3 	15221135004 	3
  18. 	Edit 	Delete 	4 	22000007009 	8
  19.  
  20.  
  21. Query ane
  22. SELECT a.kodebarang, sum( a.total ) , b.kodebarang, sum( b.total2 )
  23. FROM test1 a
  24. LEFT OUTER JOIN test2 b ON a.kodebarang = b.kodebarang
  25. GROUP BY a.kodebarang, b.kodebarang
  26. UNION
  27. SELECT a.kodebarang, sum( a.total ) , b.kodebarang, sum( b.total2 )
  28. FROM test1 a
  29. RIGHT OUTER JOIN test2 b ON a.kodebarang = b.kodebarang
  30. GROUP BY a.kodebarang, b.kodebarang
  31.  
  32. Hasilnya
  33.  
  34.  
  35. kodebarang 	sum( a.total ) 	kodebarang 	sum( b.total2 )
  36. 15060001001 	4 	15060001001 	4
  37. 15221135001 	1 	NULL 	NULL
  38. 22000007001 	2 	NULL 	NULL
  39. NULL 	NULL 	15221135004 	3
  40. NULL 	NULL 	22000007009 	8
  41.  
  42.  
  43. row(s) starting from record # 
  44.  
  45.  
  46.  
  47.  
  48. ======== Seharusnya hasil yang diinginkan ====
  49.  
  50.  
  51. kodebarang 	sum( a.total ) 	kodebarang 	sum( b.total2 )
  52. 15060001001 	2 	15060001001 	2
  53. 15221135001 	1 	NULL 	NULL
  54. 22000007001 	2 	NULL 	NULL
  55. NULL 	NULL 	15221135004 	3
  56. NULL 	NULL 	22000007009 	8
  57.  
  58.  
  59. row(s) starting from record # 
  60.  
Maaf, Untuk Memberikan Komentar Anda Harus Login !!!

CV Asktech Indonesia

Cari Produk Digital, Freelancer Terbaik dan Terpercaya di Indonesia

CV Asktech Indonesia
Jl. Angkasa Puri, Perundam V, Tunggul Hitam
Padang 25176 - Indonesia

Telp./Fax. (0751) 461695
Phone/WA. +62-812-6777-1344
E-mail robby.prihandaya@gmail.com

Tentang Kami

Jam Layanan

  • Senin - Jum'at: 09:00 - 17:00 WIB
  • Sabtu - Minggu: 09:00 - 15:00 WIB

Knowledgebase

Online