Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Stored procedure to get the lowest price of each product, allowing optional parameters

I’m building a stored procedure that returns the lowest price of each product and show their respective store info and date. This SP should allow optional parameters for StoreCity and ProdType. I’m a bit stuck on the SELECT query.

These are the tables:

+-----------+
| Product   |
+-----------+
| id_prod   |
| prod_type |
+-----------+

+-----------+
| Store     |
+-----------+
| idStore   |
| StoreName |
| StoreCity |
+-----------+

+---------------+
| Product_Store |
+---------------+
| fkStore       |
| fkProd        |
| PriceProd     |
| PriceDate     |
+---------------+

At first, I was trying this:

SELECT fkProd, MIN(PriceProd)
FROM Product_Store
GROUP BY fkProd;

to get this:

+---------+---------------+
| fkProd  | MIN(PriceProd)|
+---------+---------------+
|       1 |          1.11 |
|       2 |          0.11 |
|       3 |          1.11 |
|       4 |          0.11 |
+---------+---------------+

It’s close to what I expected, but I also need the fkStore and PriceDate. So I use the query below, but (as far as I can see) it won’t allow me to use parameters (they should be in the first SELECT within the WITH clause, right?)

WITH q AS (
    SELECT 
        fkStore, 
        fkProd, 
        PriceProd, row_number() over (partition by fkProd order by PriceProd ASC) MinPrice,
        PriceDate
    FROM Product_Store)
SELECT 
    Store.StoreName, 
    Product.ProdType, 
    PriceProd, 
    PriceDate
FROM q
INNER JOIN Store ON q.fkStore = Store.idStore
INNER JOIN Product ON q.fkProd = Product.idProd
WHERE MinPrice = 1
Tags:

3

Answers


  1. 0

    This should work:

    SELECT Product_Store.fkPROD,MIN(PriceProd),PriceDate,StoreName AS "MinPrice" 
FROM Product JOIN Product_Store ON id_prod=fkPROD 
JOIN Store ON idStore=fkStore 
GROUP BY Product_Store.fkPROD;
    Login or Signup to reply.
  2. 0

    If you are looking for a stored procedure-based answer below would do.

    This is achieved using a common table transaction also known as
    (CTE)

    Refer the below SQL code:

    CREATE PROCEDURE LowestProductPrices
    @StoreCity NVARCHAR(100) = NULL,
    @ProdType NVARCHAR(100) = NULL
AS
BEGIN
    WITH MinPriceCommonTableExpression AS (
        SELECT 
            fkProd, 
            fkStore, 
            MIN(PriceProd) AS MinPrice,
            MIN(PriceDate) AS MinPriceDate 
        FROM 
            Product_Store
        GROUP BY 
            fkProd, fkStore
    ),
    ProductWithMinPrice AS (
        SELECT 
            ps.fkProd, 
            ps.fkStore, 
            ps.PriceProd, 
            ps.PriceDate
        FROM 
            Product_Store ps
        INNER JOIN 
            MinPriceCommonTableExpression mp ON ps.fkProd = mp.fkProd AND ps.PriceProd = mp.MinPrice
    )
    SELECT 
        p.id_prod AS ProductID,
        p.prod_type AS ProductType,
        s.StoreName,
        s.StoreCity,
        pm.PriceProd,
        pm.PriceDate
    FROM 
        ProductWithMinPrice pm
    INNER JOIN 
        Store s ON pm.fkStore = s.idStore
    INNER JOIN 
        Product p ON pm.fkProd = p.id_prod
    WHERE 
        (@StoreCity IS NULL OR s.StoreCity = @StoreCity) AND
        (@ProdType IS NULL OR p.prod_type = @ProdType)
    ORDER BY 
        p.id_prod;
END
    Login or Signup to reply.
  3. 0

    In order to get your desired result with optional parameters you can create an SP like:

    DELIMITER 

CREATE PROCEDURE GetLowestPricePerProduct(
    IN StoreCity VARCHAR(100),
    IN ProdType VARCHAR(100)
)
BEGIN
    SELECT 
        p.id_prod,
        p.prod_type,
        s.StoreName,
        s.StoreCity,
        ps.PriceProd,
        ps.PriceDate
    FROM 
        Product p
        INNER JOIN Product_Store ps ON p.id_prod = ps.fkProd
        INNER JOIN Store s ON ps.fkStore = s.idStore
    WHERE 
        (s.StoreCity = StoreCity OR StoreCity IS NULL)
        AND (p.prod_type = ProdType OR ProdType IS NULL)
        AND ps.PriceProd = (
            SELECT MIN(ps2.PriceProd)
            FROM Product_Store ps2
            WHERE ps2.fkProd = p.id_prod
        )
    ORDER BY 
        p.id_prod, ps.PriceDate;
END 

DELIMITER ;

    Call SP like:

    CALL GetLowestPricePerProduct(NULL, NULL);

    Or

    CALL GetLowestPricePerProduct('New York', 'Electronics');

    and get underlying results:
    | id_prod | prod_type | StoreName | StoreCity | PriceProd | PriceDate |
    | ——- | ———– | ——— | ——— | ——— | ———- |
    | 1 | Electronics | BestBuy | New York | 279.99 | 2024-02-01 |
    | 2 | Groceries | Target | Chicago | 13.99 | 2024-01-01 |
    | 3 | Clothing | Target | Chicago | 44.99 | 2024-01-01 |

    Hope this is what you needed

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search