How do I access a field inside a subquery within a subquery?

Question

Here's the query:

SELECT `o`.`price`,
       `us`.`avatar` AS `url`,
       `u`.`id`,
       `u`.`username`
FROM orders AS o
INNER JOIN `users` AS `u` ON `u`.`id` =
  (SELECT user_id
   FROM
     (SELECT s.vol,
             t_vol,
             user_id
      FROM orders s
      LEFT JOIN
        (SELECT Sum(vol) AS t_vol,
                order_id
         FROM order_transactions
         GROUP BY order_id) t ON s.id = t.order_id
      WHERE o.price = s.price HAVING vol - Ifnull(t_vol, 0) > 0) adsads
   ORDER BY s.vol DESC LIMIT 1)
INNER JOIN `user_settings` AS `us` ON `us`.`user_id` = `u`.`id`
WHERE `o`.`price` IN (10.00000000)
GROUP BY `o`.`price`

I know it's a big one, but the issue is quite simple. o.price (WHERE o.price = s.price on the 5th to last line) isn't accessible because it is inside a nested subquery. I really don't know how to fix this, making an alias for o.price does not help.

Edit: I found out that I need a correlated subquery. Any ideas on how to restructure my query are much appreciated.

Edit 2: Here's a description of what I need to do...

• Compute the remaining volume of every single order
• For each distinct price, find the avatar of the user who owns the order with the largest remaining volume

Remaining volume: Each order can have zero or more order_transactions. The remaining volume of an order is the original volume of that order minus the sum of all order_transactions belonging to that order.

---

SQL FIDDLE: http://sqlfiddle.com/#!2/52a3d5

Answer 1

I actually think this is a rather ugly query but I don't see any other way to do it in MySQL:

SELECT prices.price, u.id, u.username, us.avatar AS url
FROM (
  SELECT mo.price, MIN(mo.user_id) u_id
  FROM (
    SELECT p.price, MAX(p.is_left) max_left
    FROM (
      SELECT o.price, o.vol - SUM(IFNULL(t.vol, 0)) is_left
      FROM orders o 
      LEFT JOIN order_transactions t on t.order_id = o.id
      GROUP BY o.price, o.vol, o.id
    ) p
    GROUP BY p.price
  ) m
  JOIN (
    SELECT o.id, o.user_id, o.price, o.vol - SUM(IFNULL(t.vol, 0)) is_left
    FROM orders o 
    LEFT JOIN order_transactions t on t.order_id = o.id
    GROUP BY o.id, o.user_id, o.price, o.vol
  ) mo ON mo.price = m.price AND mo.is_left = m.max_left
  GROUP BY mo.price
) prices
JOIN users u ON u.id = prices.u_id
JOIN user_settings us ON us.user_id = u.id

So I'm getting all the orders with what's left for transaction, then getting the MAX() per price. Then I'm getting all the orders again and JOINing on price and on the is_left being the same as the just-determined MAX(is_left). This could be more than 1 result, so I get the MIN(user_id) and GROUP BY price. Then join on this MIN(user_id) to finally have 1 user per price.

Here's the SqlFiddle I made, I've added an order for a different price to test.