CODEWITHSUNDEEP
mysqlsql
user862010
user862010

Reputation:

Find products that are sold at a price less than mine

I have a table of products, suppliers and prdtFrn as follows:

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

And I am looking for a SQL query that will return all products that are sold at a price less than mine (andrey). In this example, I would want to get product "X", because lucas is selling it for less than I am.

Upvotes: 0

Views: 236

Answers (4)

Bohemian
Bohemian

Reputation: 424983

A lot of the other answers seem complicated, but the answer is simple:

select distinct p1.* 
from prdtfrn p1
join prdtfrn p2 on p1.pid = p2.pid and p2.fid != 1 and p2.price < p1.price
where p1.fid = 1; // fid=1 is known to be 'Audrey'

This query lists all products that are sold cheaper elsewhere.

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Here is a query to get the info you're looking for. As there might be products that other suppliers have on sale and you don't, I thought you might be interested in finding those out too.

This is the query that you're asking for (without the products other suppliers have and you don't):

select sp2.pid, p.name as ProductName, sp2.price, s2.name as SupplierName
from prdtFrn sp2 join (
  select sp.pid, sp.price from suppliers s
  join prdtFrn sp on sp.fid = s.fid
  where s.name = 'Andrey'
) as AndreysProducts
on AndreysProducts.pid = sp2.pid
join products p on sp2.pid = p.pid
join suppliers s2 on s2.fid = sp2.fid
where sp2.price < AndreysProducts.price

Example

This is the query that you might be interested in (with the products other suppliers have and you don't):

select sp2.pid, p.name as ProductName, sp2.price, s2.name as SupplierName
from prdtFrn sp2 left join (
  select sp.pid, sp.price from suppliers s
  join prdtFrn sp on sp.fid = s.fid
  where s.name = 'Andrey'
) as AndreysProducts
on AndreysProducts.pid = sp2.pid
join products p on sp2.pid = p.pid
join suppliers s2 on s2.fid = sp2.fid
where sp2.price < AndreysProducts.price or AndreysProducts.pid is null

Example

Upvotes: 1

John Woo
John Woo

Reputation: 263693

I assumed that you are comparing to many suppliers (not only to lucas) so this is my query. Try this one:

SELECT  e.name,
        g.name,
        f.price
FROM suppliers e INNER JOIN prdtFrn f ON
        e.fid = f.fid
     INNER JOIN products g ON
        f.pid = g.pid
WHERE   e.name <> 'Andrey' AND  -- this selects all products which is not yours
        f.pid IN                -- the selected products should match your products
            (SELECT c.pid       -- this subquery selects all your products
                    c.name,     
                    b.price
            FROM suppliers a INNER JOIN prdtFrn b ON
                    a.fid = b.fid
                 INNER JOIN products c ON
                    b.pid = c.pid
            WHERE  a.name = 'Audrey') d AND
        f.price < d.price       -- product is less than yours

Upvotes: 1

cheeken
cheeken

Reputation: 34655

Just select from the prdtFrn table twice. From there, the query is straightforward.

I've included an untested example below. A corresponds to the competitors' products and B corresponds to yours.

SELECT 
    suppliers.name, 
    A.pid, 
    A.price 
FROM 
    prdtfrn AS A, 
    prdtfrn AS B, 
    suppliers 
WHERE 
    A.price < B.price
    AND A.pid = B.pid
    AND B.fid = 1
    AND A.fid = suppliers.fid;

Upvotes: 1

Related Questions