CODEWITHSUNDEEP
mysqljoincreate-view
Kb Aaron Myrna
Kb Aaron Myrna

Reputation: 3

Create view from 3 different tables?

I'm trying to create a view in MySQL to include from 3 different tables.

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) 
);

The VIEW I'm trying to create:

create view film_production as 
select 
    title, 
    actor_first, 
    actor_last, 
    director_first, 
    director_last, 
    Production_id, 
    release_date 
from    film, 
        actor, 
        director
;

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 join or some kind.

Upvotes: 0

Views: 769

Answers (2)

zedfoxus
zedfoxus

Reputation: 37029

Have you thought of adding referential keys between actor and film, and director and film tables?

Actor

CREATE TABLE ACTOR (
    ID        INT PRIMARY KEY NOT NULL, 
    FIRSTNAME VARCHAR(25), 
    LASTNAME  VARCHAR(30), 
    DOB       DATE
);

Director

CREATE TABLE DIRECTOR (
    ID         INT PRIMARY KEY NOT NULL, 
    FIRSTNAME  VARCHAR(25), 
    LASTNAME   VARCHAR(30) 
);

Film with connections to actor and director

CREATE TABLE FILM (
    FILM_ID         INT PRIMARY KEY NOT NULL, 
    TITLE           VARCHAR(50), 
    RELEASE_DATE    DATE, 
    DIRECTOR_ID     INT, 
    GENRE_ID        INT, 
    PRODUCTION_ID   INT,
    ACTOR_ID        INT,
    CONSTRAINT FK_FILM_DIRECTOR_ID FOREIGN KEY (DIRECTOR_ID)
    REFERENCES DIRECTOR(ID),
    CONSTRAINT FK_FILM_ACTOR_ID FOREIGN KEY (ACTOR_ID)
    REFERENCES ACTOR(ID)
);

View combining the 3 tables

create view film_production as 
select 
    title, 
    a.firstname as actor_firstname, 
    a.lastname as actor_lastname, 
    d.firstname as director_firstname, 
    d.lastname as directory_lastname, 
    Production_id, 
    release_date 
from       film f
left join actor a on f.actor_id = a.id
left join director d on f.director_id = d.id;

Example: http://sqlfiddle.com/#!9/9fe1e

Note that I have used INT instead of DECIMAL for ID fields and VARCHAR instead of CHAR fields. This may help in allocating the right space for most general purpose uses.

Upvotes: 1

Chizzle
Chizzle

Reputation: 1715

Yes, your view should use joins.

BUT, it looks like you are missing a table, something like film_actor. Using the missing table, and assuming you are trying to get the cast and directors of each movie it would be 

SELECT 
  FILM.*
 ,ACTOR_FIRST + ' ' + ACTOR_LAST AS actor
 ,DIRECTOR_FIRST + ' ' + DIRECTOR_LAST AS director
FROM FILM
INNER JOIN FILM_ACTOR fa ON fa.FILM_ID = FILM.FILM_ID
INNER JOIN ACTOR ON ACTOR.ACTOR_ID = FILM_ACTOR.ACTOR_ID
INNER JOIN DIRECTOR d ON d.DIRECTOR_ID = FILM.DIRECTOR_ID

Like I mentioned, you are missing a table that would hold multiple actors and associate them to one movie: a one to many relationship.

Upvotes: 2

Related Questions