SQL SELECT * WHERE value NOT IN list and IN another list

Question

I want to select a SKU that is not in ('DC01','5000'), but is in ('1003','1039','1012') where the SUM is greater than 3. I intend to get back zero records, as the SKU '000000001041106003' is in '1003' with StockOnHand greater than 3, but has a StoreID of 'DC01', however the SKU value of '000000001041106003' is returned. That SKU has a StoreID of 'DC01' and '1003'.

What do I need to do in order to get the desired outcome?

productName                    SKU  StoreId StockOnHand webenabled

.Speedo Yarn    000000001041106001  1003        1         1
.Speedo Yarn    000000001041106002  1003        3         1
.Speedo Yarn    000000001041106003  1003        4         1
.Speedo Yarn    000000001041106003  DC01        0         1


SELECT DISTINCT(SKU)
FROM etlStoreAssortment 
WHERE StoreId NOT IN ('DC01','5000') 
AND StoreId IN ('1003','1039','1012') GROUP BY SKU HAVING SUM(StockOnHand) > 3

im not sure which dialect you are using but anyway i would write "...NOT <name> IN <value>..." so "...NOT StoreID IN(....". — user2504380
– user2504380, Commented Oct 20, 2014 at 9:12
According to the data and your query, the output is correct. Each record is validated separately against the conditions. — Bernd Linde
– Bernd Linde, Commented Oct 20, 2014 at 9:12

Thorsten Kettner · Accepted Answer · 2014-10-20 09:13:02Z

1

WHERE looks at a single record. There is one record for '000000001041106003' where StoreId NOT IN ('DC01','5000') and StoreId IN ('1003','1039','1012'). What you are looking for though is where at least one record per group has or doesn't have a certain value. Use HAVING for this:

SELECT DISTINCT(SKU)
FROM etlStoreAssortment 
GROUP BY SKU 
HAVING SUM(StockOnHand) > 3
AND MAX(CASE WHEN StoreId IN ('DC01','5000') THEN 1 ELSE 0 END) = 0
AND MAX(CASE WHEN StoreId IN ('1003','1039','1012') THEN 1 ELSE 0 END) = 1;

answered Oct 20, 2014 at 9:13

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SELECT * WHERE value NOT IN list and IN another list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related