title: MySQL referencing nested query result in the SELECT?
body: <p>Say you have two tables with columns:</p>
  
  <ul>
  <li>table1: id, data</li>
  <li>table2: id, t1_ref (which is a FK to table1), some_other_column</li>
  </ul>
  
  <p>Now I can write something like (I know that this can be written differently, more efficient, etc, not looking for those):</p>
  
  <pre><code>SELECT t1.data,
     (SELECT count(*)
      FROM table2 t2
      WHERE t2.t1_ref = t1.id) AS nested_result
  FROM table1 t1;
  </code></pre>
  
  <p>My question is, where can I use 'nested_result' <strong>in the rest of the main query</strong>? Can I use it in the FROM (in another nested select for instance)? Or in the WHERE? Or in a GROUP BY? Or on a ORDER BY? Anywhere else?</p>
  
  <p>For example MySQL doesn't seem to like:</p>
  
  <pre><code>SELECT t1.data,
     (SELECT count(*)
      FROM table2 t2
      WHERE t2.t1_ref = t1.id) AS nested_result
  FROM table1 t1
  WHERE nested_result &gt; 100;
  </code></pre>
  
  <p>but what are the general rules here?</p>