Query to exclude products that do not meet a condition-CodePudding

I need help to write a SQL query that will return SKUs that are in stock on specific warehouses as well as on stores.

I need to exclude all SKUs that are not available in the warehouses.

So far I have a basic query:

SELECT 
    Sap_Category,
    SapSubCategory,
    Material_ID
    Description,
    Store_Code 
    Store_Descr,
    Store_Inventory,
    Store_Type,
FROM 
    db.Stock

I tried adding a where clause but it doesn't seem to work.

WHERE
    Material_ID = (SELECT Material_ID FROM db.Stock 
                   WHERE (Store_Type = "Warehouse") AND Store_Inventory > 0)

CodePudding user response：

As t@Pontnou & @FDavidov mentioned in the comments if you have multiple values for a column use IN command in the where clause.

Also, there is a comma (,) missing after Material_ID in your select statement. This might not give any syntax error but will display different column names (description) for Material_ID values.

SELECT 
  Sap_Category,
  SapSubCategory,
  Material_ID,
  Description,
  Store_Code 
  Store_Descr,
  Store_Inventory,
  Store_Type,
FROM 
  db.Stock
WHERE
  Material_ID IN (SELECT Material_ID FROM db.Stock 
               WHERE Store_Type = "Warehouse" AND Store_Inventory > 0)

If you have unique values for Material_ID in your table you can use your subquery where clause conditions in the main query.

SELECT 
  Sap_Category,
  SapSubCategory,
  Material_ID,
  Description,
  Store_Code 
  Store_Descr,
  Store_Inventory,
  Store_Type,
FROM 
  db.Stock
WHERE Store_Type = "Warehouse" AND Store_Inventory > 0