Как сделать join строки из другой таблицы с минимальным значением?

Question

[Денис Шпуганич]

Дано:
1) Таблица продуктов product

| id |  title  |
| 1  |продукт 1|
| 2  |продукт 2|

2) Таблица цен поставщиков supplier_price

|product_id|supplier_id|price|
|    1     |     1     | 10  |
|    1     |     2     | 5   |
|    2     |     1     | 15  |

Задача:
Выбрать все поля продуктов с минимальными ценами и соответствующими id поставщиков:

|product_id|supplier_id|price| id |  title  |
|    1     |     2     | 5   | 1  |продукт 1|
|    2     |     1     | 15  | 2  |продукт 2|

Что я пробовал?
1) Использовал JOIN

SELECT
    supplier_product.product_id,
    supplier_product.supplier_id,
    MIN(supplier_product.price)
FROM product
LEFT JOIN supplier_product ON product.id = supplier_product.product_id
group by supplier_product.product_id, supplier_product.supplier_id
#group by supplier_product.product_id

Проблема:
Если добавить в group by поле supplier_id, в выборке задваиваются продукты с разными поставщиками
Если не добавлять (выключить full_only_group_by в sql_mode) то значения supplier_id и price будут не согласованы (мускул случайным образом выбирает значение)

2) Использовал вложенный SELECT

SELECT 
    product.*, 
    min_price
FROM product 
LEFT JOIN (
    SELECT 
        supplier_product.product_id,
        MIN(supplier_product.price) AS min_price
    FROM supplier_product
    GROUP BY supplier_product.product_id
  ) AS sp
ON product.id = sp.product_id

Проблема: По сути та же. Минимальную цену получаю, но связанного с ней поставщика - нет

Так есть ли способ сделать так, чтоб мускул группировал правильно?
То есть чтобы при выборе минимальной цены он брал соответствующие значения id поставщика

Answer 1

[Rsa97]

SELECT `p`.`product_id`, `p`.`supplier_id`, `p`.`price`, `i`.`title`
  FROM (
    SELECT `product_id`, MIN(`price`) AS `price`
      FROM `supplier_price`
      GROUP BY `product_id`
  ) AS `mp`
  JOIN `supplier_price` AS `p` ON `p`.`product_id` = `mp`.`product_id`
    AND `p`.`price` = `mp`.`price`
  JOIN `product` AS `i` ON `i`.`id` = `mp`.`product_id`

Или через ROLLUP

SELECT `p`.`product_id`, `p`.`supplier_id`, `p`.`price`, `i`.`title`
  FROM (
    SELECT `product_id`, `supplier_id`, `price`
      FROM `supplier_price`
      GROUP BY `product_id`, `supplier_id`, `price` WITH ROLLUP
      HAVING `price` = MIN(`price`)
  ) AS `p`
  JOIN `product` AS `i` ON `i`.`id` = `mp`.`product_id`

Comments

[Денис Шпуганич]

первое работает как надо, но при совпадении цен всё равно выводит лишние строки

[Rsa97]

Денис Шпуганич, А по какому признаку выбирать одну компанию из нескольких с одинаковой ценой?