0

I have two tables: products and prices

products

  • id (PK)
  • name

prices

  • id (PK)
  • product_id (FK > products)
  • price
  • originalPrice

Each product might have multiple prices. What I want to achieve is a query that returns me all products on-sale with its cheapest price.

  • on-sale = price < originalPrice
  • if a product is not on-sale, it should not be included in the results
  • if a product has multiple prices that qualify for on-sale, only return the cheapest price.

The resulting table should have these columns

  • products.id
  • products.name
  • prices.id
  • prices.price
  • prices.originalPrice

With my attempts I'm ending up with this issue: #1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tbl.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. Please note that I cannot change the config.

MySQL version: 5.7.22

I have uploaded a SQL export with sample data here: https://www.dropbox.com/s/6ucdv6592dum6n6/stackoverflow_export.sql?dl=0

cweinberger
  • 3,518
  • 1
  • 16
  • 31

4 Answers4

2
select pro.name, MIN(pri.price) from products pro
inner join price pri on pri.product_id = pro.id 
where pri.price < pri.originalPrice 
group by pro.name

heres a shot without any data :p may need a little tweaking

t..
  • 1,101
  • 1
  • 9
  • 22
1

Hope this works for you

SELECT *,MIN(price)  FROM (
SELECT name, products.id,price
FROM products
INNER JOIN productItems
   ON products.id = productItems.productId
WHERE price < originalPrice
ORDER BY (price-originalPrice)
) as tbl GROUP BY id;

OR

SELECT *,MIN(diff)  FROM (
SELECT name, products.id,price,(price-originalPrice) as "diff"
FROM products
INNER JOIN productItems
   ON products.id = productItems.productId
WHERE price < originalPrice
ORDER BY products.id,(price-originalPrice)
) as tbl GROUP BY id;
Rohit.007
  • 3,414
  • 2
  • 21
  • 33
  • I had tried something like that before. Unfortunately I end up with: `#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tbl.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` _(using mysql 5.7.22, I cannot change the config)_ – cweinberger Jun 15 '18 at 19:55
  • It means you cannot use other fields when you are using `GROUP BY` with `aggregation` function. In this case either you need to handle the values at application end. – Rohit.007 Jun 15 '18 at 20:07
  • Have some tricks but not working with the latest MySql https://stackoverflow.com/a/36033983/8179245 – Rohit.007 Jun 15 '18 at 20:08
  • Thanks! I have updated my question.. unfortunately I cannot update the config. – cweinberger Jun 16 '18 at 06:41
1

Try this:

SELECT * 
FROM `products` pro
JOIN price pri on pri.productId = pro.id
WHERE pri.price < pri.originalPrice
AND pri.price = 
( 
    SELECT min(p.price) 
    FROM price p 
    WHERE p.productId = pro.id AND p.price < p.originalPrice 
)
Yosra Hamza
  • 529
  • 3
  • 5
1

This works with that dropbox link you gave: http://sqlfiddle.com/#!9/a6306d/3

 select pro.name, MIN(pri.price) from products pro
    inner join price pri on pri.productId = pro.id 
    where pri.price < pri.originalPrice 
    group by pro.name
t..
  • 1,101
  • 1
  • 9
  • 22