Harrobbed Harrobbed - 1 year ago 54
SQL Question

How to join three tables with a NOT IN CLAUSE

Scenario ... A STORE gets a LIST. The 'LIST' basically is a collection of SKUs that the store stocks that needs to be counted.

When a store starts counting the SKUs from a given List assigned to it, the information is saved in another table called 'StoreCycles' which records the ListId, StoreName and the Dates the counting started and completed.

These are my tables.

Table 1. Table with 'Lists' which have a primary key 'ListId'

ListId ListName
1 abc
2 def
3 ghi

Table 2. 'Stores' -- Each list from the above table ('Lists' Table) is assigned to one or more stores.
The ListId in the 'Stores' Table is the primary key of the 'Lists' tabel. The Listid and LineId together make up the foreign key.

ListId LineId StoreName
1 1 StoreA
1 2 StoreD
2 1 StoreB
2 2 StoreC
2 3 StoreA
3 1 StoreA

Table 3. 'StoreCycles' -- that saves when the list assigned to a store was started counting and when it was completed.

ListId StoreName StartDate CompleteDate
1 StoreA 2016-7-22 2016-7-22
2 StoreA 2016-7-22
2 StoreC 2016-7-22

At any time I want to pull up those list names that have not been completed , i.e they have a null complete date.

This is my query:

Select T0.ListId,
T0.ListNaame ,
From Lists T0
JOIN Stores T1 On T0.ListId = T1.ListId
LEFT JOIN StoreCycles T2 ON T0.ListId = T2.ListId
WHERE T1.StoreName = 'StoreA'
AND T0.ListId NOT IN (SELECT ListId FROM StoreCycles WHERE CompleteDate IS NOT NULL)


ListId ListName StartDate
2 def 2016-7-22
3 ghi NULL

BUT The result that I get is this

ListId ListName StartDate
2 def NULL
2 def NULL
3 ghi NULL

Answer Source

Just passing by and dumping SQL.

Go on, nothing to see.

There is no bug hunt going on here.

declare @Lists table (ListId int primary key, ListName varchar(20));
insert into @lists values (1,'abc'),(2,'def'),(3,'ghi');

declare @Stores table (ListId int, LineId int, StoreName varchar(20));
insert into @Stores values 

declare @StoreCycles table (ListId int, StoreName varchar(20), StartDate date, CompleteDate date);
insert into @StoreCycles values

FROM @Stores S   
JOIN @Lists L On (S.ListId  = L.ListId)
LEFT JOIN @StoreCycles SC ON (S.ListId  = SC.ListId AND S.StoreName = SC.StoreName)
WHERE S.StoreName = 'StoreA' 
AND SC.CompleteDate IS NULL;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download