title: Create view from 3 different tables?
body: <p>I'm trying to create a view in MySQL to include from 3 different tables.</p>
  
  <pre><code>CREATE TABLE ACTOR (
      ACTOR_ID    DECIMAL (2,0) PRIMARY KEY NOT NULL, 
      ACTOR_FIRST CHAR(25), 
      ACTOR_LAST  CHAR(30), 
      DOB         DATE
  );
  
  CREATE TABLE DIRECTOR (
      DIRECTOR_ID     DECIMAL(2,0) PRIMARY KEY NOT NULL, 
      DIRECTOR_FIRST  CHAR(25), 
      DIRECTOR_LAST   CHAR(30) 
  );
  
  CREATE TABLE FILM (
      FILM_ID         DECIMAL(2,0) PRIMARY KEY NOT NULL, 
      TITLE           CHAR(50), 
      RELEASE_DATE    DATE, 
      DIRECTOR_ID     DECIMAL(2,0), 
      GENRE_ID        CHAR(10), 
      PRODUCTION_ID   CHAR(3) 
  );
  </code></pre>
  
  <p>The <code>VIEW</code> I'm trying to create:</p>
  
  <pre><code>create view film_production as 
  select 
      title, 
      actor_first, 
      actor_last, 
      director_first, 
      director_last, 
      Production_id, 
      release_date 
  from    film, 
          actor, 
          director
  ;
  </code></pre>
  
  <p>This gives me like 192 results. But I only have 3 actors, with 8 movies with 8 directors, and 6 production_id's. Another thing to note is 1 movie has 2 of the 3 actors in it, so I should have at least 9 movies that show not 192. I don't know what I'm doing wrong. I just cant figure this out. I'm thinking maybe a <code>join</code> or some kind. </p>