YVS1102 YVS1102 - 4 months ago 7
SQL Question

Check if data exists SQL

I'm trying to create a

checking
from my table. I have three tables
table hari
,
table transaksi
&
table Outlet
. My table hari only one field in it called with 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
table 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 not have transaction . 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?

Answer

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
Comments