title: Mysql Reference subquery result in parent where clause
body: <p>In our school, when a student is good, they get given a virtual pound (or dollar) which is stored in a database. </p>
  
  <p>This is my query:</p>
  
  <pre><code>SELECT s.chosen_name, s.chosen_surname, s.regId, s.admission_number, 
      (
          SELECT SUM(a.pounds_amount) 
          FROM tbl_pounds_log a 
          WHERE a.student_id=l.student_id
      ) AS total_pounds, 
      (
          SELECT SUM(b.pounds_amount) 
          FROM tbl_pounds_log b 
          WHERE b.student_id=l.student_id 
          AND b.staff_id=:staffId 
          AND b.action_timestamp&gt;(UNIX_TIMESTAMP()-3600) 
      ) AS available_pounds 
  FROM TBL_student s 
  LEFT JOIN tbl_pounds_log l 
      ON l.student_id=s.admission_number 
  WHERE ((s.chosen_name LIKE :termOne AND s.chosen_surname LIKE :termTwo) 
          OR (s.chosen_name LIKE :termThree AND s.chosen_surname LIKE :termFour))
      AND (total_pounds&gt;=:lowerPoundLimit 
          AND total_pounds&lt;=:upperPoundLimit) 
  GROUP BY s.admission_number 
  ORDER BY s.chosen_surname ASC, s.chosen_name ASC  
  LIMIT 0,10
  </code></pre>
  
  <p>(I'm using PHP PDO to perform the query hence the :text placeholders).</p>
  
  <p>I'm having a bit of an issue when it comes to the WHERE condition in the parent query.</p>
  
  <p>Where it says:</p>
  
  <pre><code>... AND (total_pounds&gt;=:lowerPoundLimit and total_pounds&lt;=:upperPoundLimit)
  </code></pre>
  
  <p>The total_pounds field is a column result of a subquery, however when I run the query it keeps coming up with:</p>
  
  <pre><code>Unknown column 'total_pounds' in 'where clause'
  </code></pre>
  
  <p>Does anyone know a solution to this?</p>
  
  <p>Thanks</p>
  
  <p>Phil</p>