I have the following MySQL table of sample data:
create table prices ( id INTEGER PRIMARY KEY AUTO_INCREMENT, store_id INTEGER NOT NULL, item INTEGER NOT NULL, price FLOAT NOT NULL ); insert into prices values(1,1,1,1.25); insert into prices values(2,1,2,.99); insert into prices values(3,2,2,1.20); insert into prices values(4,3,3,3.25); insert into prices values(5,3,1,1.35);
I am looking for a query to select any store_id
that a company would not use to purchase product. For example in prices
, store_id
2 would not be used because item
2 can be bought for cheaper from store_id
1.
How would I go about this? Thank you.
Advertisement
Answer
With this query:
select item, min(price) minprice from prices group by item
you get the minimum price for each item.
If you left join
the table prices
to the above query and keep only the non matching rows (meaning rows that don’t have the cheapest price for that item), you will get all the stores and items that can be found cheaper:
select p.* from prices p left join ( select item, min(price) minprice from prices group by item ) g on g.item = p.item and g.minprice = p.price where g.item is null
See the demo.
Results:
| id | store_id | item | price | | --- | -------- | ---- | ----- | | 3 | 2 | 2 | 1.2 | | 5 | 3 | 1 | 1.35 |
If you want only the stores that you would not buy anything from:
select p.store_id from prices p left join ( select item, min(price) minprice from prices group by item ) g on g.item = p.item and g.minprice = p.price group by p.store_id having sum(g.item is not null) = 0
See the demo.
Results:
| store_id | | -------- | | 2 |