How to correctly formulate a SQL query?

Question

MySQL

There are 3 tables:
1) goods goods(id,name)
2) shops shops(id, name)
3) residues(shop_id, good_id, quantity,
price
) quantity) of each product
For example:
id[1] = 1, quantity[1] = 2;
id[2] = 5, quantity[2] = 1;
id[3] = 6, quantity[3] = 1;
It is necessary to select the identifiers of the stores where all the products are and sort them in ascending order of the total cost (quantity[1] * price[1] + ... + quantity[N] * price[N])
It just doesn’t reach me (rather knowledge is not enough), how to do it in the minimum number of actions (and preferably in one request) :(

Answer 1

For each item, find the shop_id and price for those stores that have enough stock, then find the intersection (JOIN) of those stores. The resulting table is sorted by value. Since the number of goods can be different, the query must be built each time from fragments. The result should be something like

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