CODEWITHSUNDEEP
mysqlsql
user862010
user862010

Reputation:

Who sell with lowest price

I have a database with tables:

suppliers:
 fid , name
   1 | 'Andrey' 
   2 | 'lucas'

products:
 pid , name
   1 | 'X' 
   2 | 'Y' 

prdtFrn:
 pid , fid , price
  ---------------- supplier 'andrey'
   1 |  1  | 19.00 
   2 |  1  | 16.00 
  ----------------- supplier 'lucas'
   1 |  2  | 14.00 
   2 |  2  | 18.00

Querying the products, I get all products that have registred, but I need know who sells current product of 'product table' as lowest price, I don't know how to write the query

Upvotes: 0

Views: 297

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753555

As with any complex SQL, build it up, step-by-step.

SELECT s.name AS s_name, p.name AS p_name, sp.price
  FROM suppliers AS s
  JOIN prdtFrn   AS sp ON s.fid = sp.fid
  JOIN products  AS p  ON p.pid = sp.pid

This gives you the list of seller, product and price. Now, presumably, for each product, you want the details of the supplier name who sells the product for the smallest price:

SELECT p_name, MIN(price) AS min_price
  FROM (SELECT s.name AS s_name, p.name AS p_name, sp.price
          FROM suppliers AS s
          JOIN prdtFrn   AS sp ON s.fid = sp.fid
          JOIN products  AS p  ON p.pid = sp.pid
       ) AS spp

This gives you the minimum price for each product. Now you need to join that result with the first query, to yield the answer:

SELECT spp.p_name, spp.s_name, min_price
  FROM (SELECT s.name AS s_name, p.name AS p_name, sp.price
          FROM suppliers AS s
          JOIN prdtFrn   AS sp ON s.fid = sp.fid
          JOIN products  AS p  ON p.pid = sp.pid
       ) AS spp
  JOIN (SELECT p_name, MIN(price) AS min_price
          FROM (SELECT s.name AS s_name, p.name AS p_name, sp.price
                  FROM suppliers AS s
                  JOIN prdtFrn   AS sp ON s.fid = sp.fid
                  JOIN products  AS p  ON p.pid = sp.pid
               ) AS spp
       ) AS mp
    ON mp.p_name = spp.p_name AND mp.min_price = spp.price;

Then, when it's working, you can optimize...for example, the minimum price calculation doesn't need the supplier information:

SELECT spp.p_name, spp.s_name, mp.min_price
  FROM suppliers AS s
  JOIN prdtFrn   AS sp ON s.fid = sp.fid
  JOIN (SELECT pid, MIN(price) AS min_price
          FROM prdtFrn
         GROUP BY pid
       ) AS mp
    ON mp.pid = sp.pid AND mp.min_price = sp.price;

My first query went awry because I went for more information than was needed in the very first query; this is much simpler. So, the lessons here are:

  1. Build up the query piece-by-piece.
  2. Don't be satisfied with the first draft.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

select p.* 
from (
    select pid, min(price) as MinPrice
    from prdtFrn
    group by pid
) pm
inner join prdtFrn p on pm.pid = p.pid and pm.MinPrice = p.price

You will see multiple rows per product where two suppliers are selling a product at the same price.

Upvotes: 3

Related Questions