How do I get All actors that haven't appeared in R rated films

Question

I want to get All actors that haven't appeared in R rated films

It was easy to do with sub-queries

select *
from actor
where actor_id not in
  (select actor_id
   from actor
     inner join film_actor using (actor_id)
     inner join film using (film_id)
   where rating = 'R');

I am unable to do it with joins -

I have done this till now -

select distinct(a.actor_id), a.first_name, a.last_name
from actor as a
  left join
    (film_actor as fa
       inner join film as f
         on fa.film_id = f.film_id)
  on a.actor_id = fa.actor_id
  where f.rating = 'R'
  order by actor_id

In the above query I get all the actors who have worked in R rated films, but now I am not sure how to get the non R rated films

Can someone help me do this with joins?

Not sure what more information is needed? Whoever asked to close it, it would be helpful if you could comment and ask about it? — Gautam, Jun 01 '20 at 09:56
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Jun 01 '20 at 10:10
Why do you have `INT4` in `film` and `actor`, yet `INT2` in `film_actor`? — MatBailie, Jun 01 '20 at 10:15
@MatBailie I understand it's incorrect, it' s a DB I picked up somewhere. — Gautam, Jun 01 '20 at 10:19
What is your 1 specific researched non-duplicate question? It's not clear here what queries you have do what parts of your overall goal & what things you are stuck at. Are you asking about rewriting joins to use subqueries, or what? PS Re this code, we can't cut & paste & run. Also, if we could, the image would be redundant. But anyway: Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, Jun 01 '20 at 10:22
@Gautam . . . I removed the inconsistent database tags. Only tag with the database you are really using. — Gordon Linoff, Jun 01 '20 at 10:32
Does this answer your question? . [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/q/4707673/3404097) — philipxy, Jun 01 '20 at 11:03

MatBailie · Accepted Answer · 2020-06-01T10:19:34.150

WHERE clauses are applied after joins.

The implact here is that your LEFT JOIN has effectively been turned back in to an INNER JOIN:
- The LEFT JOIN returns some records with NULLs
- The WHERE clause excludes the NULLs

Instead, you must filter to the R rated films before the JOIN on to the actors table.

select distinct(a.actor_id), a.first_name, a.last_name
from actor as a
  left join
    (film_actor as fa
       inner join film as f
         on fa.film_id = f.film_id
         and f.rating = 'R')         -- R Rating filter here
  on a.actor_id = fa.actor_id
  where f.film_id IS NULL            -- where the actor does NOT map to such films
  order by actor_id

The DISTINCT shouldn't be required either, the join is 1 actor to 0 films, so no duplication.

Also, I find your style very hard to read. Try spacing things out a bit more, and making the level of indentation wore consistent?

SELECT
    a.actor_id,
    a.first_name,
    a.last_name
FROM
  actor   AS a
LEFT JOIN
(
        film_actor   AS fa
    INNER JOIN
        film         AS f
            ON  fa.film_id = f.film_id
            AND f.rating   = 'R'
)
    ON  a.actor_id = fa.actor_id
WHERE
    f.film_id IS NULL
ORDER BY
    a.actor_id

Finally, NOT EXISTS is often much faster than NOT IN for these cases (anti-semi-joins).

SELECT
    *
FROM
    actor
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            film_actor
        INNER JOIN
            film
                USING (film_id)
        WHERE
                film.rating         = 'R'
            AND film_actor.actor_id = actor.id
    )

I was not able to remember how to apply the `R Rating filter`. Thank you this works perfectly. — Gautam, Jun 01 '20 at 10:18

score 0 · Answer 2 · answered Jun 01 '20 at 18:02

0

So, if you know how to find all actors in R-rated films, then SQL subtraction is a good solution. Select all actors minus Select all actors in R-rated files.

answered Jun 01 '20 at 18:02

Marvin Krone

1

How do I get All actors that haven't appeared in R rated films

2 Answers2