title: MySQL subquery to refer to field in parent query
body: <p>I am building a query that performs some filtering on rating data.</p>
  
  <p>Suppose I have a simple table called <code>ratings</code> like the following, storing data from an online rating tool:</p>
  
  <pre>
  +----------------+----------------+--------+
  | page_title     | timestamp      | rating |
  +----------------+----------------+--------+
  | Abc            | 20110417092134 | 1      |
  | Abc            | 20110418110831 | 2      |
  | Def            | 20110417092205 | 3      |
  +----------------+----------------+--------+
  </pre>
  
  <p>I need to extract pages with a high frequency of low values in the latest 10 ratings, and limit this query to pages that produced a volume of at least 20 ratings in the preceding week. This is the <strike>ridiculously long</strike> query I came up with:</p>
  
  <pre><code>SELECT a1.page_title, COUNT(*) AS rvol, AVG(a1.rating) AS theavg, 
  (
       SELECT COUNT(*) FROM
       (
           SELECT * FROM ratings a2 WHERE a2.page_title = a1.page_title 
           AND DATE(timestamp) &lt;= '2011-04-24' ORDER BY timestamp DESC LIMIT 10
       ) 
       AS latest WHERE rating &gt;=1 AND rating &lt;=2 ORDER BY timestamp DESC
  )
  AS lowest FROM ratings a1
  WHERE DATE(a1.timestamp) &lt;= "2011-04-24" AND DATE(a1.timestamp) &gt;= "2011-04-17" 
  GROUP BY a1.page_title HAVING COUNT(*) &gt; 20
  </code></pre>
  
  <p>the top level query looks for pages with more than 20 ratings in the week terminating on 2011-04-24, the subquery is supposed to retrieve the number of ratings with values between [1,2] from the latest 10 ratings of each article from the top level query.</p>
  
  <p>MySQL complains that a1.page_title in the WHERE clause of the subsubquery is an unknown column, I suspect this is because a1 is not defined as an alias in the second-level query, but only in the top-level query, but I am clueless how to fix this.</p>
  
  <p>(edited)</p>
  
  <p>I am adding as an explanation of my suspect above regarding cross-level referencing another query which works absolutely fine, note that here a1 is not defined in the subquery but it is in the immediate parent:</p>
  
  <pre><code>SELECT a1.page_title, COUNT(*) AS rvol, AVG(a1.rating) AS theavg, 
  (
      SELECT COUNT(*) FROM ratings a2 WHERE DATE(timestamp) &lt;= '2011-04-24'
      AND DATE(timestamp) &gt;= '2011-04-17' AND rating &gt;=1 
      AND rating &lt;=2 AND a2.page_title = a1.page_title
  ) AS lowest FROM ratings a1 
  WHERE DATE(a1.timestamp) &lt;= '2011-04-17' AND DATE(a1.aa_timestamp) &gt;= '2011-04-11' 
  GROUP BY a1.page_title HAVING COUNT(*) &gt; 20
  </code></pre>