CODEWITHSUNDEEP
sqlsql-serverleft-join
Harobed
Harobed

Reputation: 167

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  ,
T2.StartDate   
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)

RESULT SHOULD BE >>

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

Upvotes: 2

Views: 66

Answers (3)

LukStorms
LukStorms

Reputation: 29647

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 
(1,1,'StoreA'),
(1,2,'StoreD'),
(2,1,'StoreB'),
(2,2,'StoreC'),
(2,3,'StoreA'),
(3,1,'StoreA');

declare @StoreCycles table (ListId int, StoreName varchar(20), StartDate date, CompleteDate date);
insert into @StoreCycles values
(1,'StoreA','2016-7-22','2016-7-22'),
(2,'StoreA','2016-7-22',null),
(2,'StoreC','2016-7-22',null);

SELECT
L.ListId,
L.ListName,
SC.StartDate
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;

Upvotes: 1

Malk
Malk

Reputation: 11983

Maybe another way to look at it is to return all rows where either the StartDate is NULL (no records) or the CompleteDate is NULL (incomplete records). Also, if the foreign key is using 2 columns you probably want to use both columns in the JOIN.

SELECT  T0.ListId,
        T0.ListName,
        T2.StartDate
FROM    Lists T0
            JOIN Stores T1   
                 ON T1.ListId = T0.ListId
            LEFT JOIN StoreCycles T2
                 ON T2.ListId = T1.ListId
                AND T2.LineId = T1.LineId
WHERE T1.StoreName = 'StoreA'
AND (T2.CompleteDate IS NULL OR T2.StartDate IS NULL)

Upvotes: 0

Matt
Matt

Reputation: 14341

Select 
    l.ListId
    ,l.ListNaame 
    ,sc.StartDate
From
    Lists l
    JOIN Stores s
    ON  l.ListId  = s.ListId
    AND s.StoreName = 'StoreA' 
        LEFT JOIN StoreCycles sc 
        ON  s.ListId  = sc.ListId
        AND s.LineId = sc.LineId
WHERE
      sc.CompleteDate IS NULL

You are already doing joins if you build them correctly you will not need a select or not in in your where clause. Also your relationship for StoreCycles seems to be wrong because you are trying to go straight from Lists to StoreCycles but Stores is the intermediary table.

Also just a thought why not use table aliases that will help you know what table you are referring to rather than t0, t1, t2 such as l, s, sc....

Upvotes: 0

Related Questions