CODEWITHSUNDEEP
mysqlsqljoin
Harambe Attack Helicopter
Harambe Attack Helicopter

Reputation: 780

Replacing id with name form another table in existing query

here's my query for getting the remaining stocks of each Product_Id. However, I'm not sure how to go about adding Product_Name from Product table. I just want to replace the i.Product_Id with p.Product_Name with p being the alias of Product. I know that the Product p should be inserted in the FROM but not sure how to connect it with the given query.

By the way, Product.Id = Inventory.Product_Id

SELECT 
i.Product_Id,
i.Stocks,
s.Sales,
i.Stocks - s.Sales AS Remaining
FROM (SELECT product_id, COALESCE(SUM(quantity),0) AS Stocks FROM inventory GROUP BY product_id) i
LEFT JOIN (SELECT product_id, COALESCE(SUM(quantity),0) AS Sales FROM sales_detail GROUP BY product_id ) s
USING(product_id);

Thanks in advance guys!

UPDATE!! The query below is my updated one, can someone check if I linked the product correctly with the existing JOIN USING, thanks!

SELECT 
i.Product_Id,
p.Product_Name,
i.Stocks,
s.Sales,
i.Stocks - s.Sales AS Remaining
FROM (SELECT product_id, COALESCE(SUM(quantity),0) AS Stocks FROM inventory GROUP BY product_id) i
LEFT JOIN (SELECT product_id, COALESCE(SUM(quantity),0) AS Sales FROM sales_detail GROUP BY product_id ) s
USING(product_id)
JOIN Product p
ON i.Product_Id=p.Id;

Upvotes: 0

Views: 169

Answers (1)

Barmar
Barmar

Reputation: 781210

You just need to JOIN with the Products table.

SELECT 
    p.Product_Name,
    i.Stocks,
    s.Sales,
    i.Stocks - s.Sales AS Remaining
FROM (SELECT product_id, COALESCE(SUM(quantity),0) AS Stocks FROM inventory GROUP BY product_id) i
JOIN Products AS p USING (product_id)
LEFT JOIN (SELECT product_id, COALESCE(SUM(quantity),0) AS Sales FROM sales_detail GROUP BY product_id ) s
USING(product_id)

Upvotes: 1

Related Questions