CODEWITHSUNDEEP
sqlsql-server
Hammas
Hammas

Reputation: 1214

Sql Not IN and Not Exists Clauses are not working with Where condition

I am trying to get Items which were sold before a given date plus Items which are never sold.
So, Using below queries i get Items which were sold before a given date but i don't get Items which were never sold.

When i separately execute NOT IN and NOT EXIST Queries i get Items in result that don't exist in ItemsSold table. What's wrong here ?

Full Query with NOT EXISTS 

select MAx(ITM.Name) as Name,Max(ITM.ItemID) as ID,Max(ItemsSold.UpdatedDate) as LastSaleDate from Items ITM
Inner join ItemsSold on ItemsSold.ItemID = ITM.ItemID
where convert(varchar,ItemsSold.UpdatedDate,111) < '2019/04/01'
or  NOT EXISTS (select ItemsSold.ItemID from ItemsSold where ItemsSold.ItemID=ITM.ItemID) 
Group by ITM.ItemID order by ITM.ItemID;

Full Query with NOT IN 

select MAx(ITM.Name) as Name,Max(ITM.ItemID) as ID,Max(ItemsSold.UpdatedDate) as LastSaleDate from Items ITM
Inner join ItemsSold on ItemsSold.ItemID = ITM.ItemID
where convert(varchar,ItemsSold.UpdatedDate,111) < '2019/04/01'
or ITM.ItemID NOT IN (select ItemsSold.ItemID from ItemsSold) 
Group by ITM.ItemID order by ITM.ItemID

NOT IN separately 

select Items.Name,Items.ItemID from Items where Items.ItemID NOT IN (select ItemsSold.ItemID from ItemsSold) order by ItemID

Executing NOT IN and NOT EXISTS Query separately does return actual correct data but with full query it doesn't.

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You appear to want items that were only sold before a certain date or never sold. That is, no items were sold later:

Use not exists:

select i.*
from items i
where not exists (select 1
                  from itemssold its
                  where its.ItemId = i.ItemId and
                        its.UpdatedDate >= '2019-04-01'
                 );

You want appropriate indexes no matter how you phrased the query if performance is a consideration. For this, the index would be on itemssold(ItemId, UpdatedDate). This should be much faster than the an aggregation form of the query.

If the question is more how it is phrased: There exists an order before the date or no orders at all, then you would use two comparisons:

select i.*
from items i
where exists (select 1
              from itemssold its
              where its.ItemId = i.ItemId and
                    its.UpdatedDate < '2019-04-01'
             ) or
      not exists (select 1
                  from itemssold its
                  where its.ItemId = i.ItemId
                 );

Upvotes: 1

Thom A
Thom A

Reputation: 96027

With no sample data or expected results this is a guess, however, based purely on your existing SQL and your comment "No ! i want a list of items that were sold before a given date plus items that were never sold ." this might be what you are after:

SELECT I.ItemID
FROM Items I
     LEFT JOIN ItemsSold ItS ON I.ItemID = ItS.ItemID
GROUP BY I.ItemID
HAVING MAX(ItS.UpdatedDate) < '20190401' --Proper date logic, got rid of CONVERT
    OR MAX(ItS.ItemID) IS NULL;

It might be that you want MIN(ItS.UpdatedDate), depending on if you want items that were only sold before 2019-04-01 or if you want items that were at least first sold before 2019-04-01 (impossible to know from the lack of description).

If not, I refer to my prior comment "we need sample data here that replicates the "problem" and expected results.".

Upvotes: 2

Related Questions