I'm programming a C# Windows Forms Application in Visual Studio and I'm trying to get data about minimum prices of products in supermarket2 and supermarket 3 from shopping lists.
The first step I need to take is to make a correct SELECT-query that returns this data from my local MySQL-database.
I have the following columns in my prices-table:
I also have a supermarkets-table which includes 3 supermarkets:
A product has a price for each supermarket in the supermarkets-table
I'm using the following query to let the database return the data I want:
SELECT pro.name, MIN(p.price) AS 'Lowest Price', p.supermarket_id
FROM (
SELECT i.product_barcode
FROM shopping_list_items i
WHERE i.shopping_list_id = 95
) s
JOIN prices p ON s.product_barcode = p.barcode
JOIN products pro ON s.product_barcode = pro.barcode
GROUP BY s.product_barcode;
The above query returns the name of the product, the lowest price of the product and the supermarket_id. But the supermarket_id is always 1 although the price of the product is not in supermarket1 but in supermarket3.
So my question now is how does my query has to be in order to get the supermarket_id along with the minimum price of the products in a specific grocery list
For this, you should use a subquery to get all the barcodes and their minimum prices using group by and having clause. And then you can use the resulta to further join with the prices table.
select p2.price as lowest_price, p2.barcode as prod_barcode , p2.supermarket_id as supermarket from
(
select min(price) as min_p, barcode min_b from prices p1 where p1.supermarket_id!=1 group by barcode having barcode in
(
SELECT i.product_barcode FROM shopping_list_items i WHERE i.shopping_list_id = 95
)
)
m join prices p2 where m.min_p=p2.price and m.min_b= p2.barcode;