title: How do I access a field inside a subquery within a subquery?
body: <p>Here's the query:</p>
  
  <pre><code>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) &gt; 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`
  </code></pre>
  
  <p>I know it's a big one, but the issue is quite simple. <code>o.price</code> (<code>WHERE o.price = s.price</code> 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 <code>o.price</code> does not help.</p>
  
  <p>Edit: I found out that I need a correlated subquery. Any ideas on how to restructure my query are much appreciated.</p>
  
  <p>Edit 2: Here's a description of what I need to do...</p>
  
  <ul>
  <li>Compute the <em>remaining volume</em> of every single order</li>
  <li>For each distinct price, find the avatar of the user who owns the order with the largest <em>remaining volume</em></li>
  </ul>
  
  <p><strong>Remaining volume:</strong> 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.</p>
  
  <hr>
  
  <p><strong>SQL FIDDLE: <a href="http://sqlfiddle.com/#!2/52a3d5" rel="nofollow">http://sqlfiddle.com/#!2/52a3d5</a></strong></p>