title: SQL query confusion - actors, roles, movies
body: <p>I have three tables: </p>
  
  <p>ACTOR</p>
  
  <pre><code>id 
  nameofactor
  </code></pre>
  
  <p>MOVIE </p>
  
  <pre><code>id
  nameOfmovie 
  </code></pre>
  
  <p>CASTS</p>
  
  <pre><code>actorid 
  movieid
  role 
  </code></pre>
  
  <p>I want to show how the names of actors who had played more than one role in a movie</p>
  
  <p>Here is what I have tried:</p>
  
  <pre><code>select 
      nameOfactor, nameOfmovie, CASTS.role
  from 
      ACTOR, MOVIE, CASTS
  where 
      ACTOR.id = CASTS.actorid 
      and CASTS.mid = MOVIE.movieid
  group by 
      fname, lname, name, role
  having 
      count(pid) &gt;= 2; 
  </code></pre>
  
  <p>But it doesn't work.</p>
  
  <p>I guess the problem is in this way I have to put "role" in group by, but as I need to show roles, somehow I have to this</p>
  
  <p>I dont know how to fix this query. I would be happy if anyone can help</p>
  
  <p>Thanks in advance</p>