Home > 808831, Computer Programming > Query SQL #1

Query SQL #1


Ada seorang teman menanyakan query kepada saya

SELECT
    Products.ProductID,
    Products.ProductName,
    SaldoIn.[In],
    SaldoOut.Out,
    Saldo.saldo
FROM
    Products
    LEFT JOIN SaldoIn ON Products.ProductID = SaldoIn.CodProd
    LEFT JOIN SaldoOut ON Products.ProductID = SaldoOut.CodProd
    LEFT JOIN Saldo ON Products.ProductID = Saldo.codProd
GROUP BY
    Products.ProductID,
    Products.ProductName,
    SaldoIn.[In],
    SaldoIn.Bln,
    SaldoOut.Out,
    SaldoOut.Bln,
    Saldo.saldo,
    Saldo.bln
HAVING
    SaldoIn.Bln="12"
    AND SaldoOut.Bln="12"
    AND Saldo.bln)="12";

hasilnya ketika salah satu tabel tidak menemukan record yang bersangkutan, bisa jadi hasilnya kosong

untuk itu bagaimana bila query tersebut saya modifikasi seperti ini

SELECT
    Products.ProductID,
    Products.ProductName,
    SaldoIn.[In],
    SaldoOut.Out,
    Saldo.saldo
FROM
    Products
    LEFT JOIN SaldoIn ON Products.ProductID = SaldoIn.CodProd and SaldoIn.Bln=12
    LEFT JOIN SaldoOut ON Products.ProductID = SaldoOut.CodProd and SaldoOut.Bln=12
    LEFT JOIN Saldo ON Products.ProductID = Saldo.codProd and Saldo.BlnJ=12
GROUP BY
    Products.ProductID,
    Products.ProductName,
    SaldoIn.[In],
    SaldoIn.Bln,
    SaldoOut.Out,
    SaldoOut.Bln,
    Saldo.saldo,
    Saldo.bln

atau selengkapnya dalam Script SQL server adalah:

declare @Bulan as Byte;
set @Bulan=12;

SELECT
    Products.ProductID,
    Products.ProductName,
    SaldoIn.[In],
    SaldoOut.Out,
    Saldo.saldo
FROM
    Products
    LEFT JOIN SaldoIn ON Products.ProductID = SaldoIn.CodProd and SaldoIn.Bln=@Bulan
    LEFT JOIN SaldoOut ON Products.ProductID = SaldoOut.CodProd and SaldoOut.Bln=@Bulan
    LEFT JOIN Saldo ON Products.ProductID = Saldo.codProd and Saldo.BlnJ=@Bulan
GROUP BY
    Products.ProductID,
    Products.ProductName,
    SaldoIn.[In],
    SaldoIn.Bln,
    SaldoOut.Out,
    SaldoOut.Bln,
    Saldo.saldo,
    Saldo.bln

Go
  1. No comments yet.
  1. No trackbacks yet.

Please kindly Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: