CODEWITHSUNDEEP
sqlsortingsql-server-2016
coblenski
coblenski

Reputation: 1189

SQL check against 2 columns

I have a database with 1,362 different items, which can be stored in any one of 112 different warehouses.

I'd like to write something that would return the names of each item which has a stock value of 0 in all of the different warehouses.

I have the following already:

SELECT OITW.[ItemCode] AS [Item Code], INV1.[Dscription] AS [Description],
       OITW.[WhsCode] AS [Warehouse], SUM(OITW.[OnHand]) AS [On Hand]
FROM OITW
  INNER JOIN INV1 ON INV1.[ItemCode] = OITW.[ItemCode] 
WHERE INV1.[Dscription] LIKE 'BA SQUID ASSASSIN -Case%'
GROUP BY OITW.[ItemCode], INV1.[Dscription], OITW.[WhsCode]
ORDER BY [Warehouse], [On Hand]

Which returns:

enter image description here

This item would not be applicable because it has stock in 2 warehouses, for example.

I hope this question makes sense. Let me know if not and I will try to clarify.

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

Assuming the stock value is never negative, you can do:

SELECT INV1.[ItemCode] AS [Item Code], INV1.[Description] AS [Description],
FROM OITW INNER JOIN
     INV1
     ON INV1.[ItemCode] = OITW.[ItemCode] 
WHERE INV1.[Description] LIKE 'BA SQUID ASSASSIN -Case%'
GROUP BY OITW.[ItemCode], INV1.[Description], 
HAVING MAX([ON HAND]) = 0;

The changes from your query:

  • The GROUP BY includes only the item and description, not the warehouse and on-hand amounts.
  • The SELECT is modified to match the GROUP BY.
  • The HAVING clause enforces your condition.
  • I would advise you not to have spaces in your table names. Use simple names so escaping the column names is not necessary.

Upvotes: 1

Related Questions