Get Minimum Price + Mysql Query

I want minimum price based on shop type.

Here Bronze shops is default or static.
So When product has price for all three shops like gold, silver and bronze then I get the minimum price from gold and bronze shops.
But when product has not gold shops and it has silver shops then I get the minimum price from silver and bronze shops.
Suppose gold and silver both shops has not price then I get the minimum price from bronze shops.

please look this below example.

1x Gold = 25 USD
1x Gold = 15 USD
1x Silver = 5 USD
1x Silver = 35 USD
1x Bronze = 10 USD

Here I want the price from Bronze shop because here Gold shops has price then I compare only Gold and Bronze shops.

In this example:

1x Silver = 25 USD
1x Silver = 5 USD
1x Bronze = 15 USD
1x Bronze = 35 USD

I want the "Silver" shop because that is the cheapest and Gold shop is not there then I compare only Silver and Bronze.

Is this possible using mysql single select query?
Thanks.

 

I personally wouldn't bother.

Get the minimum price for each using 1 SQL query and then deal with the logic of the Bronze -v- Silver -v- Gold outside of the SQL

 

Yes, it is possible. You need to use a "join" statement. Since there are a small amount of values you can use a cross join... "SELECT * FROM TableA CROSS JOIN TableB". Then sort what you want.

 

Bronze shops is always compare to get minimum price.

Product Table:

ID | Name
1 | Test Product1
2 | Test Product2
3 | Test Product3


Shop Table:

ID | Name | ShopType
1 | Shop1 | Gold
2 | Shop2 | Bronze
3 | Shop3 | Silver
4 | Shop4 | Gold

ProductShop Table:

ID | ProductId | ShopId | Price
1 | 1 | 3 | 31.5
2 | 1 | 4 | 28.0
3 | 1 | 2 | 65.0
4 | 2 | 1 | 55.0
5 | 2 | 2 | 12.0
6 | 3 | 1 | 62.0
7 | 3 | 3 | 10.0
8 | 3 | 4 | 72.0


For Product ID 1:
Availabel shops are :
ShopId | ShopType
3 | Silver
4 | Gold
2 | Bronze


2 | 1 | 4 | 28.0
3 | 1 | 2 | 65.0

Here Gold shop is exist so don't count silver shop and compare only "Gold" and "Bronze" shop and get the minimum price = 28.0

For Product ID 2:
Availabel shops are :
ShopId | ShopType
1 | Gold
2 | Bronze

4 | 2 | 1 | 55.0
5 | 2 | 2 | 12.0

Minimum Price: 12.0


For Product ID 3:
Availabel shops are :
ShopId | ShopType
1 | Gold
3 | Silver
4 | Gold

6 | 3 | 1 | 62.0
8 | 3 | 4 | 72.0

Here also Gold shop exist so don't compare silver shop and get minimum price: 62.0

If gold shop is not exist then and then count silver shop for compare.
And Bronze shops are default when bornze shops is exist then its always count to get minimum price.

Please give me idea to write query based on above table.

 

What outputs do you want to get? Simply the min price? The min price for each ShopType? The names of the shops?

 

I want minimum price based on shop type.

When gold hops is exist then neglect silver shop and only match Gold and bronze shop.

And Gold shop is not exist and silver is there then match silver and bronze shop.

 

As said previously, best practice would have the logic not being done at the SQL level and so the SQL could be:

SELECT S.ShopType, Min(PS.Price) AS MinPrice
FROM ProductShopTable AS PS INNER JOIN ShopTable AS S ON PS.ShopID = S.ID
WHERE PS.ProductID)=@ProductID
GROUP BY S.ShopType;

Code (markup):

Your otherwise looking at a stored procedure to have anything near efficient. I use MS SQL but I am sure MySQL wouldn't be too far off it.

CREATE PROCEDURE sp_GetInventory
    @ProductID Int
AS
    DECLARE @GoldCount Int

    SET @GoldCount = SELECT Count(S.ShopType) FROM ProductShopTable AS PS INNER JOIN ShopTable AS S ON PS.ShopID = S.ID WHERE PS.ProductID=@ProductID AND S.ShopType='Gold';

    IF @GoldCount > 0
        BEGIN
            SELECT Min(PS.Price) AS MinPrice
            FROM ProductShopTable AS PS INNER JOIN ShopTable AS S ON PS.ShopID = S.ID
            WHERE PS.ProductID=@ProductID AND NOT S.ShopType='Silver';
        END
    ELSE
        BEGIN
            SELECT Min(PS.Price) AS MinPrice
            FROM ProductShopTable AS PS INNER JOIN ShopTable AS S ON PS.ShopID = S.ID
            WHERE PS.ProductID=@ProductID; 
        END
GO

Code (markup):

You would be better off having the StoreType as an Integer rather than text too