Create view problems

Question

I need to create a view that contains the id and names of actors that are in a drama film that released in 2010.

I wrote this:

CREATE VIEW DRAMA_ACTORS 
AS 
    SELECT 
        ACTOR_ID, FIRST_NAME, LAST_NAME
    FROM 
        ACTOR, FILM_ACTOR, FILM, FILM_CATEGORY, CATEGORY
    WHERE 
        ACOTR.ACTOR_ID = FILM_ACTOR.ACTOR_ID
        AND FILM_ACTOR.FILM_ID = FILM.FILM_ID
        AND FILM.FILM_ID = FILM_CATEGORY.FILM_ID
        AND FILM_CATEGORY.CATEGORY_ID = CATEGORY.CATEGORY_ID
        AND CATEGORY.NAME = 'Drama'
        AND FILM.RELEASE_YEAR = '2010';

The schema is like this:

Thanks for any help

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Oct 21 '15 at 04:43
Do SELECT DISTINCT to avoid duplicates (if an actor has been in several movies that year.) Do you store years as text?!? Don't do that, use integer! — jarlh, Oct 21 '15 at 06:57

jhmt · Answer 1 · 2015-10-21T07:01:09.627

1

You might want to use JOIN like this:

SELECT a.ACTOR_ID, a.FIRST_NAME, a.LAST_NAME
FROM FILM AS f
JOIN FILM_ACTOR AS fa ON fa.FILM_ID = f.FILM_ID
JOIN ACOTR AS a ON a.ACTOR_ID = fa.ACTOR_ID
JOIN FILM_CATEGORY AS fc ON fc.FILM_ID = f.FILM_ID
JOIN CATEGORY AS c ON c.CATEGORY_ID = fc.CATEGORY_ID
WHERE c.NAME = 'Drama' AND f.RELEASE_YEAR = '2010'

If you got duplicate results, use DISTINCT or GROUP BY in your query.

edited Oct 21 '15 at 07:01

answered Oct 21 '15 at 03:34

jhmt

1,401
1
11
15

what's the difference between inner join and join? thanks – cdl2333 Oct 21 '15 at 03:58
The top answer of this question (http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join) should be better than my explanation:) – jhmt Oct 21 '15 at 04:00
so in this case, can i use join as well? – cdl2333 Oct 21 '15 at 04:12
Why GROUP BY when no aggregate functions?!? – jarlh Oct 21 '15 at 06:54
JOIN is just short form for INNER JOIN. (And LEFT JOIN is short form for LEFT OUTER JOIN.) – jarlh Oct 21 '15 at 06:55
@jarlh I intended it to just avoid duplication. I updated my answer. – jhmt Oct 21 '15 at 07:02

score 0 · Answer 2 · answered Oct 21 '15 at 07:19

It's better (In my opinion) to avoid creating duplicates in the first place, rather than to eliminate them afterwards.

If we want to identify actors who've been in a drama in 2010, it shouldn't matter if they've been in multiple films - we just want to assert that at least one such film exists:

SELECT 
    ACTOR_ID, FIRST_NAME, LAST_NAME
FROM 
    ACTOR a
WHERE
    EXISTS (SELECT *
       FROM FILM_ACTOR fa
               INNER JOIN
            FILM f
               ON fa.FILM_ID = f.FILM_ID
               INNER JOIN
            FILM_CATEGORY fc
               ON
                  fc.FILE_ID = f.FILM_ID
               INNER JOIN
            CATEGORY c
               ON
                  fc.CATEGORY_ID = c.CATEGORY_ID
    WHERE
        a.ACTOR_ID = fa.ACTOR_ID
    AND c.NAME = 'Drama'
    AND f.RELEASE_YEAR = '2010'
);

Create view problems

2 Answers2