title: Why correlated subquery result cannot be used in outer query?
body: <p>Why correlated subquery result cannot be used in outer query?</p>
  
  <p>In below query I want to access the count in the inner corrleated query in outer query. But it is giving error. </p>
  
  <p><strong>What am I doing wrong?</strong></p>
  
  <p>referenct - <a href="http://sqlzoo.net/wiki/More_JOIN_operations#Looking_at_the_id_field" rel="nofollow">http://sqlzoo.net/wiki/More_JOIN_operations#Looking_at_the_id_field</a>.</p>
  
  <p>Question- 14 (Obtain a list in alphabetical order of actors who've had at least 30 starring roles.)</p>
  
  <pre><code>select distinct a.name  , t.count
  from actor a 
  join casting c 
  on (a.id= c.actorid) 
  where 25&lt; (select count(movieid) as count 
      from casting c1 
      where c1.actorid= a.id) as t
  order by a.name
  </code></pre>
  
  <p>Below query works fine.</p>
  
  <pre><code>select distinct a.name, (select count(movieid) as count 
      from casting c1 
      where c1.actorid= a.id)
  from   actor a 
  join casting c 
  on (a.id= c.actorid) 
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!4/4e7be/8" rel="nofollow">http://sqlfiddle.com/#!4/4e7be/8</a></p>