Как правильно сформулировать SQL запрос?

Question

[artyomkulakov]

Есть 3 таблицы:
1) товары goods(id,name)
2) магазины shops(id, name)
3) остатки residues(shop_id, good_id, quantity, price)

Задача следующая:
Есть произвольное количество идентификаторов (id) товаров и необходимое количество (quantity) каждого из товаров
Например:
id[1] = 1, quantity[1] = 2;
id[2] = 5, quantity[2] = 1;
id[3] = 6, quantity[3] = 1;
Необходимо выбрать идентификаторы магазинов, где есть все товары и отсортировать их в порядке возрастания суммарной стоимости (quantity[1] * price[1] + ... + quantity[N] * price[N])
До меня просто не доходит (скорее знаний не хватает), как можно в минимальное кол-во действий (а желательно в одном запросе) это сделать :(

Answer 1

[Rsa97]

По каждому товару находите shop_id и price для тех магазинов, где есть достаточное количество товара, затем находите пересечение (JOIN) этих магазинов. Получившуюся таблицу сортируете по стоимости. Так как число товаров может быть разным, то запрос надо строить каждый раз из фрагментов. В результате должно получиться что-то вроде

SELECT `s`.`id`, `s`.`name`
  FROM `shops` AS `s`
    JOIN `residues` AS `r1` 
      ON `r1`.`shop_id` = `s`.`id` AND `r1`.`good_id` = 1 AND `r1`.`quantity` >= 2
    JOIN `residues` AS `r2` 
      ON `r2`.`shop_id` = `s`.`id` AND `r2`.`good_id` = 5 AND `r2`.`quantity` >= 1
    ORDER BY `r1`.`price`*2+`r2`.`price*1

Comments

[artyomkulakov]

Спасибо большое, то что нужно! :)
самую малость переделал под требования - в таблице могут быть 2 одинаковых товара, но с разной ценой для одного и того же магазина. добавил в селект min(r1.price * 2 + r2.price * 1) as sum, сделал группировку по id магазина, ну и ордер уже по sum, а не по формуле.