-- apps_master.getProductPromo (PROCEDURE)
begin
SELECT p1.ProductID, bcp.CategoryID , mc.CategoryName, ProductName, ProductImage, ProductDescription,ProductIndicationUsage, ProductIngredients, ProductDosage, ProductHowToUse, ProductPackage, ProductClassification, ProductContraindication, ProductStorage, ProductRecipe, PrincipalName 
FROM bridging_classification_product_customer bcpc 
JOIN m_product1 p1 ON bcpc.ProductID = p1.ProductID
JOIN m_product2 p2 ON p1.ProductID = p2.ProductID
JOIN m_product3 p3 ON p2.ProductID = p3.ProductID
JOIN bridging_category_product bcp ON p3.ProductID = bcp.ProductID
JOIN m_category mc ON bcp.CategoryID = mc.CategoryID
WHERE ClassificationID = 1;
end