CODEWITHSUNDEEP
sqlsql-serversql-server-2008
YVS1102
YVS1102

Reputation: 2748

Check if data exists in SQL Server

I'm trying to create a checking from my table. I have three tables hari , transaksi & Outlet.

My table hari has only one column called days:

              days
    2016-07-21 00:00:00.000
    2016-07-22 00:00:00.000
    2016-07-23 00:00:00.000
    2016-07-24 00:00:00.000
    2016-07-25 00:00:00.000
    2016-07-26 00:00:00.000

My second table is transaksi:

| outlet     | Tanggal |
  K-MGAS      2016-07-23 
  K-JGMM2     2016-07-24 
  K-JGMM2     2016-07-25  
  K-CL4       2016-07-26

and the last one is outlet:

   | OutletCode |
       K-MGAS
       K-JGMM2
       K-CL4

As you can see days and outlet is master table and transaksi is transaction table. Now, I have a problem when I try to select an outlet which does not have any transactions.

My desired result is like this

| Outlet |         Days          |
  K-MGAS  2016-07-21 00:00:00.000
  K-MGAS  2016-07-22 00:00:00.000
  K-MGAS  2016-07-24 00:00:00.000
  K-MGAS  2016-07-25 00:00:00.000
  K-MGAS  2016-07-26 00:00:00.000

How can I achieve that?

Upvotes: 1

Views: 76

Answers (3)

vercelli
vercelli

Reputation: 4757

Use Cross join and then Left join 

SELECT O.OutletCode
       ,H.Days
FROM Outlet O
CROSS JOIN Hari H
LEFT JOIN Transaksi T ON T.Outlet = O.OutletCode
    AND T.Days = H.Days
WHERE T.Days IS NULL

Upvotes: 1

Paweł Dyl
Paweł Dyl

Reputation: 9143

I think you are looking something similar to following query:

SELECT *
FROM Outlet O
CROSS JOIN Hari H
WHERE NOT EXISTS (SELECT * FROM Transaksi WHERE Outlet=O.OutletCode AND Tanggal=H.Days)

Upvotes: 0

Marko Juvančič
Marko Juvančič

Reputation: 5890

Use LEFT JOIN

SELECT O.*
  FROM Outlet O
 LEFT JOIN Transaksi T ON T.Outlet = O.OutletCode
 WHERE T.Days IS NULL

Upvotes: 1

Related Questions