Mysql Достать данные из двух таблиц

[Ami]

Доброго времени суток. Возникла небольшая проблема.
Есть таблица А, в которой хранятся данные о продукте. И таблица Б, в которой хранится информация об изменениях цены. Цель достать из таблицы Б последнюю запись для конкретного продукта т.к. в таблице Б может быть не одна запись для данного товара.

Изначально у меня выводился список продуктов, я получал их id и с помощью оператора IN доставал данные из таблицы Б. Но проблема в том, что достаются все данные, а мне нужно только последние. Хоть этот метод и намного быстрей самого первого моего решения, но он мне не нравится. В таблице пока только 5к записей и нет смысла все 5к тянуть. Нужна только тысяча т.к. всего тысяча товаров.

Второй способ через INNER JOIN. Все работает. Данные я получаю, но вроде как из таблицы Б запрос достает только первое совпадение.

Как это сделать? Можно ли указать сортировку в запросе INNER JOIN? Потому что сортировка в общем запросе результатов не дает.

Пример:
Таблица А
id | product
1 samsung
2 apple
3 nokia

Таблица Б
id | price | product_id
1 100 1
2 120 1
3 115 1
4 90 2
5 85 2
6 100 3

В результате должно достать:
samsung - 115
apple - 85
nokia - 100

Вместо id я хотел брать поле date, в котором записано время в формате Unix, но создать тут еще одно поле лень Смысл в том, чтобы достать для самсунда цену в 115 т.к. она последняя для этого товара. Можно не через id, можно не через дату, можно вообще по другому.

 

[fixxxer]

а) дождаться релиза mysql 8 и использовать специально предназначенные для этого window functions
б) сделать два джойна, первый на max(id) as max_id, product_id group by product_id, второй ту же таблицу по условию id = max_id

 

[Adelf]

а нет желания последнюю(текущую) цену хранить в таблице товаров?

 

[Ami]

б) сделать два джойна, первый на max(id) as max_id, product_id group by product_id, второй ту же таблицу по условию id = max_id

Круть. Работает. Спасибо.

Вот так получилось.

INNER JOIN (SELECT MAX(id) as max_id, product_id FROM table GROUP BY product_id) s ON s.product_id=m.id
INNER JOIN table c ON c.id = max_id

А вот если в join мы можем запихнуть select, то можно ли это все в один join реализовать. Я пробовал через ORDER BY и вроде что-то получается, но не все товары выводит правильно. Да и общий вывод сортирует, что неудобно.

а нет желания последнюю(текущую) цену хранить в таблице товаров?

Ну там просто не только цена и запись будет не одна. И при удалении я хочу, чтобы оно достало предыдущее значение. У меня появилось желание вообще избавится от таблицы Б, но появилось оно после решения с join. И как быть хз

 

[halva]

SELECT product.product, (SELECT price FROM prices WHERE product_id = product.id ORDER BY id DESC LIMIT 1) AS last_price FROM product

 

[fixxxer]

Я пробовал через ORDER BY и вроде что-то получается, но не все товары выводит правильно

На самом деле, нельзя вообще. Это такая исторически сложившаяся багофича в mysql, который позволяет при группировке добавлять в select любые поля, даже не участвующие в группировке. Если даже несмотря на отсутствие группировки/агрегации на поле оказывается, что уникальность значения в рамках такого запроса гарантирована, получается фича, если же это не так, получается баг - вылазит случайное из значений.
Если включить strict mode, то такой прикол будет запрещен и mysql не даст выполнить такой запрос, как и положено в соответствии с ANSI SQL.

Чтобы делать сортировку-агрегацию внутри группы - для этого в ANSI SQL (и более "взрослых" СУБД - postgresql, mssql, oracle...) и существуют window functions, и в mysql 8 их таки сделали (в бете, вроде, уже работает), а в mariadb работает уже и в последнем релизе 10.2.

Можно, как вариант, сделать подзапросом, но скорее всего, джойны при большом размере таблицы будут эффективнее.

 

[Ami]

SELECT product.product, (SELECT price FROM prices WHERE product_id = product.id ORDER BY id DESC LIMIT 1) AS last_price FROM product

Так намного медленней джойнов и больше 1 поля не достать.

@fixxxer, спасибо

 

[halva]

@Ami, больше одного поля и правда не достать, на счет скорости не всё так однозначно.