SQL WHERE statement with multiple conditions

Question

I have a movies, credits, people and roles table (below) I would like to find the names of the people who are both actors and directors.

movies(TABLE)

id int
title

credits(TABLE)

id int
movie_id int
person_id int
role_id int

people(TABLE)

id int
name

roles(TABLE)

id int
role    (Actor, Director)

This is what I have did:

SELECT p.name, r.role, m.role_id
FROM mtm_credits m
JOIN people p ON p.id = m.person_id
JOIN roles r ON r.id = m.role_id
WHERE role = 'Director' AND role = 'Actor';

However, I am getting 0 results. Any suggestion is appreciated.

How can `role` be both `Director` and `Actor` at the same time? Depending on what you're after you need to join to the roles table twice or you need to use `OR` (or `IN`) instead of `AND`. — Aaron Bertrand, Aug 24 '20 at 21:36
And please tag only a single RDBMS, MySQL and SQL Server are different products. — Dale K, Aug 24 '20 at 21:36
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, Aug 26 '20 at 02:42
Your overall goal is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Aug 26 '20 at 02:44
Thanks. I understand the process now and refined my most recent post. — Lynn, Aug 26 '20 at 02:47
I already put the same comments on that post. PS See [How do comment @replies work?](https://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) to learn to use @x to notify one non-sole non-poster commenter x re a comment. — philipxy, Aug 26 '20 at 02:55

score 2 · Answer 1 · answered Aug 24 '20 at 21:46

The problem is that there's no single row in the resulting table whose 'role' is both 'Director' and 'Actor', because 'role' can only be one value.

It's useful to first think of what your table looks like after your joins. In this case, you have:

credits.id, credits.movie_id, credits.person_id, credits.role_id, person.id, person.name, role.id, role.name

Now a person who is both director and actor will have two rows in this table, like this:

| credits.id | credits.movie_id | credits.person_id | credits.role_id | person.id | person.name | role.id | role.name |
| 111 | 222 | 333 | 444 | 555 | 333 | N. Cage | 555 | Actor |
| 111 | 222 | 333 | 444 | 555 | 333 | N. Cage | 555 | Director |

As others have said, you need to either perform an aggregation so one row can have multiple role values, or you can get the result and filter externally

score 1 · Accepted Answer · answered Aug 24 '20 at 21:36

1

I think you want aggregation:

SELECT p.name
FROM mtm_credits m JOIN
     people p
     ON p.id = m.person_id JOIN
     roles r
     ON r.id = m.role_id
WHERE r.role IN ('Director', 'Actor')
HAVING COUNT(DISTINCT r.role) = 2;

answered Aug 24 '20 at 21:36

Gordon Linoff

1,242,037
58
646
786

I commented on a different answer as well, but there is not a step in this query to discriminate against a person that was an actor in multiple roles on the same movie. In the given example, it may very well work, since "character" is not a field on the roles table, but in a closer-to-life example, this would yield undesired behavior. – Sam Hughes Aug 24 '20 at 22:35
@SamHughes . . . The question is not asking whether a person has both roles on the same movie or not. – Gordon Linoff Aug 25 '20 at 01:08
....Uhm, you're right. I overcomplicated my reading of it by way of translating it into a real-world solution. Assuming there are only two possible role values, which was specified by the OP, then this accomplishes the desired output. – Sam Hughes Aug 25 '20 at 14:10

score 1 · Answer 3 · answered Aug 24 '20 at 21:36

The problem with your query is that you are searching for a single role that is both Director and Actor: both conditions cannot be true at the same time, so the query comes up empty.

Whenever you need to look across multiple rows, aggregation comes to mind:

SELECT p.*
FROM mtm_credits m
JOIN people p ON p.id = m.person_id
JOIN roles r ON r.id = m.role_id
WHERE r.role IN ('Director', 'Actor')
GROUP BY p.id
HAVING COUNT(DISTINCT r.role) = 2

This searches for persons that are Director or Actor, then groups rows by person; finally, the having clause allows only persons that have both roles.

Supposing an actor on a film plays multiple roles, but is not a director. Somone like Daveed Diggs would be returned as a valid result, for his performance in Hamilton, as Lafayette and Jefferson, but is not a director. The given query would not distinguish between the quality of that result and of a result for Keanu Reaves or Nicholas Cage. — Sam Hughes, Aug 24 '20 at 22:32

Sam Hughes · Answer 4 · 2020-08-24T22:26:51.683

There are two answers that use the same method, but both are wrong. If this hypothetical database contains only a single movie per person, then it will work out. If there are multiple movies where a people record is referenced, or if a single people record is referenced by credits joined to roles records of roles.role = 'Director' or roles.role = 'Actor`, this query returns a record for that invalid result. This breaks the specified behavior.

Note that @Jason-Chen explains the issue you're experiencing with your query, while I'm specifically contradicting the two answers with solutions given as of the posting-time.

Instead of simply counting the results to guarantee more than one role per people.id, which is the only guarantee the above two examples give, the admin should instead query for a connection that exists both in the list of all roles records where 'Director' is the roles.role value and all records where 'Actor' is the roles.role value.

Note that I use different names below, because I generally find the practice of single-letter aliases to be awful, and I wish instructors would instill better practices in new students. Further, I find that table names in singular form yield the most readable code.

select `person`.*
from `people` `person`
where `person`.`id` in (
    select `credit`.`person_id`
    from `roles` `role`
    join`credits` `credit`
        on `role`.`id` = `credit`.`role_id`
    where `role` like "Director" 
) and `person`.`id` in (
    select `credit`.`person_id`
    from `roles` `role`
    join`credits` `credit`
        on `role`.`id` = `credit`.`role_id`
    where `role` like "Actor"
);

I'm selecting a single value from both of the sub-queries on the roles table, which does not require an alias and instead behaves as a set. This results in very quick lookups, even for rather large tables, provided the keys used are indexed on both sides of the join.

Further, this is better than a join, because given real life examples like "Keanu Reaves", "Mel Gibson," "Tom Cruise," or other celebrities that have many Director/Actor movies under their belt, each such record would result in resultset magnification, where a single added record in data causes more than one resulting record.

SQL WHERE statement with multiple conditions

4 Answers4