title: MySQL use inner query in where clause
body: <p>I have wrote the following SQL:</p>
  
  <pre><code>SELECT s.*, (SELECT COUNT(*) FROM orders o WHERE o.schedule_id = s.schedule_id AND o.content_id = s.content_id) as numofbookings
  FROM schedules s 
  WHERE content_id = '{$contentid}'
  AND DATEDIFF(`schedule_datetime_from`, now()) &gt; 0
  AND (SELECT COUNT(*) FROM orders o WHERE o.schedule_id = s.schedule_id AND o.content_id = s.content_id) &lt; s.schedule_capacity
  ORDER BY `s`.`schedule_datetime_from` ASC
  </code></pre>
  
  <p>Although I am concerned this could be wrote better without querying the orders table twice.
  I tried <code>AND numofbookings &lt; s.schedule_capacity</code> although this give error column not found. </p>