title: Finding actor/movie rows where actor has multiple distinct roles in the same movie
body: <p>I got 3 tables, actor (id,name), movie (id,name) and casts(aid,mid,role) (aid is the actor id and mid is the movie id). I was trying to get the output like this:
  if an actor had more than 3 distinct roles in the same movie, print all the combinations, like:</p>
  
  <pre><code>-1.actor.name, movie.name, role1
  -2.actor.name, movie.name, role2
  -3.actor.name, movie.name, role3
  </code></pre>
  
  <p>My query is like this:</p>
  
  <pre><code>select a.name, m.name, x.role
  from actor a,
       movie m,
       (select distinct role
        from casts c
        where c.aid =a.id and c.mid = m.id
        group by c.aid and c.mid
        having count(distinct role) &gt;=3) as x; 
  </code></pre>
  
  <p>But I got error message:</p>
  
  <blockquote>
    <p>The multi-part identifier "m.id" could not be bound.
    The multi-part identifier "a.id" could not be bound.</p>
  </blockquote>
  
  <p>Please point out where my thought went wrong, I want to be able to do this next time. Thanks.</p>