title: SQL inner join with count() condition, and relationnal algebra
body: <p>I have these tables:</p>
  
  <ul>
  <li><p>Movies (<code>id</code>, <code>name</code>)</p></li>
  <li><p>Cast (<code>idmovie</code>, <code>actor</code>)</p></li>
  </ul>
  
  <p>And I would like to count the number of actors for each movie and then only get movies with more than 10 actors. I have a query to count the number of actors for each movie, which goes like this:</p>
  
  <pre><code>SELECT idmovie, count(actor) FROM Cast GROUP BY idmovie HAVING count(actor) &gt; 10;
  </code></pre>
  
  <p>Now, I wonder how to get that result and join it to the Movies table.
  I tried:</p>
  
  <pre><code>SELECT name FROM Movies INNER JOIN (SELECT idmovie FROM Cast GROUP BY idmovie HAVING count(actor) &gt;2) Cast ON Cast.idmovie = Movies.id;
  </code></pre>
  
  <p>But it doesn't work.</p>
  
  <p>I also have to translate it to relational algebra.</p>
  
  <pre><code>π name (σ (count(σ id = idmovie))) Movies⨝Cast
  </code></pre>
  
  <p>Which is obviously wrong...</p>
  
  <p>Any help?</p>