CODEWITHSUNDEEP
sqloracle10g
billabrian6
billabrian6

Reputation: 431

How do I retrieve all products in inventory, but also any product available with SQL?

I need to display all the the products currently in inventory for a specific warehouse, but also all products in the product table. This will include the amount in stock. If that product is not listed in the inventory for the specific warehouse, then it needs to show 0.

There are 33 different products in the product table. This is the query I use to find that info:

SELECT DISTINCT ID
FROM S_PRODUCT;

The warehouse that I'm looking at specifically contains 14 products. Here is the query I use to find that.

SELECT PRODUCT_ID
FROM S_INVENTORY
WHERE WAREHOUSE_ID = 301;

My end result should list all 33 product ID's, but only show quantity's for the items in warehouse 301's inventory. All products not in that inventory should show 0 for quantity. I have tried outer joins, and other miscellaneous queries with no luck. Please help! I should note that I'm using oracle.

Screenshot of the tables in the ERD

Screenshot of the tables in the ERD

Attempt with outer join & case.

SELECT I.PRODUCT_ID,
  CASE I.AMOUNT_IN_STOCK 
  WHEN NULL
  THEN 0
  ELSE I.AMOUNT_IN_STOCK
  END AS "IN_STOCK"
FROM S_PRODUCT P
LEFT OUTER JOIN
S_INVENTORY I
ON P.ID = I.PRODUCT_ID
WHERE I.WAREHOUSE_ID = 301;

PRODUCT_ID   IN_STOCK
---------- ----------
     20510         69 
     20512         28 
     30321         85 
     30421        102 
     30433         35 
     32779        102 
     32861         57 
     40421         70 
     40422         65 
     41010         59 
     41020         61 
     41050         49 
     41080         50 
     41100         42 

 14 rows selected

Attempt using AND instead of WHERE Note: The empty lines are also a part of output

PRODUCT_ID   IN_STOCK
---------- ----------









 20510         69 
 20512         28 
 30321         85 

 30421        102 

 30433         35 
 32779        102 
 32861         57 
 40421         70 
 40422         65 
 41010         59 
 41020         61 
 41050         49 
 41080         50 
 41100         42 









 33 rows selected

Upvotes: 1

Views: 4292

Answers (2)

xQbert
xQbert

Reputation: 35343

What does: 

SELECT P.PRODUCT_ID,
  coalesce(i.amount_in_Stock,0) AS "IN_STOCK"
FROM S_PRODUCT P
LEFT OUTER JOIN S_INVENTORY I
  ON P.ID = I.PRODUCT_ID
 AND I.WAREHOUSE_ID = 301;

return?

--NOTE The lack of WHERE clause and the movement of i.warehouse_ID to the join. This is because I.warehouse ID would eliminate records from S_PRODUCT negating the left outer join. as all i.warehouse_IDs will be null if there's no entry in s_Inventory for 301 for that product. Thus the cartesean generates, and then the where eliminates the records kept by the left join. by moving limit to the join to the filter occurs before the cartesean.

Upvotes: 1

Chris Morledge
Chris Morledge

Reputation: 71

I have not tested this but I think your answer should read something like this:

Written for MS SQL:

SELECT p.ID, CASE NVL(i.AMOUNT_IN_STOCK, '0') WHEN '0' THEN 0 ELSE i.AMOUNT_IN_STOCK END StockLevel
FROM S_PRODUCT p LEFT OUTER JOIN
     S_INVENTORY i ON p.PRODUCT_ID = i.PRODUCT_ID AND i.WAREHOUSE_ID = 301

I am selecting all the products from your product table and joining to the inventory table on the Product ID and only where the warehouse is 301. If the Inventory table is not returning a match then I am using the CASE statement to show 0 or it shows the actual value.

Upvotes: 2

Related Questions