CODEWITHSUNDEEP
mysqlsqlproductmin
user14675106
user14675106

Reputation:

Get the lowest price from a product and the provider for that price

I'm making a query for a school assignment, in which I select products which are below minimum stock, then I select the provider which delivers each product for the lowest price.

These are the tables.

+------------+     +------------------+     +------------+
| products |     | product_overview |     | provider |
+------------+     +------------------+     +------------+
| productID |     | productID |     | providerID |
| name |     | providerID |     | name |
| stock |     | price |     +------------+
| min_stock |     +------------------+
+------------+

This is what I have so far.

SELECT p.name, pr.name
FROM products p JOIN product_overview po ON p.productID = po.productID
JOIN provider l ON po.providerID = pr.providerID
WHERE p.stock < p.min_stock
GROUP BY po.productID
ORDER BY p.name;

I look for which products have stock < min_stock and then look for a provider. Currently the provider given in the output is not the provider delivering the product for the lowest price.

Upvotes: 1

Views: 35

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use not exists as follows:

SELECT p.name, pr.name provider_name, po.price
  FROM products p JOIN product_overview po ON p.productID = po.productID
  JOIN provider pr ON po.providerID = pr.providerID
 WHERE p.stock < p.min_stock
   AND not exists 
      (select 1 from product_overview poo 
        where poo.productID = po.productID 
          and poo.price < po.price)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The simplest method is to use window functions:

SELECT pp.*
FROM (SELECT p.name as product_name, pr.name as provider_name,
             ROW_NUMBER() OVER (PARTITION BY p.productID ORDER BY po.price ASC) as seqnum
      FROM products p JOIN 
           product_overview po
           ON p.productID = po.productID JOIN
           provider pr
           ON po.providerID = pr.providerID
      WHERE p.stock < p.min_stock
     ) pp
WHERE seqnum = 1
ORDER BY p.name;

Upvotes: 0

Related Questions