pset7 movies Q12

Question

I'm stuck in PSET7 SQL part 1 question 12:

I want to list all movies with 2 people inside.

The JOIN of the 3 table is set, the problem is in my WHERE statement:

WHERE people.name = 'Johnny Depp' AND people.name = 'Helena Bonham Carter'

The output is 0 => should be something like 4 or 5 movies.

I can't find how to do check for 2 conditions in the same table.

Thanks in advance,

Answer 1

It doesn't select any rows because there is no people record with both those names.

The immediate suggestion would be to use OR instead of AND.

Another option would be to use an alias of the people table to join it as "another" table, each (people and "another") joined on one of the names.

It would be easy to create a list of Johnny Depp movies by adapting the Ellen example in the lecture (around 1:33:00). Ditto a list of Helena Bonham Carter movies. All you need then is a way to produce the intersection of those lists. You might find the "in" keyword useful (around 1:31:28 in the lecture).

Answer 2

I've found GROUP BY to be very useful:

WHERE name = 'Johnny Depp' OR name = 'Helena Bonham Carter'
GROUP BY title
HAVING COUNT(*) > 1;

Answer 3

You are also able to use the INTERSECT keyword.

SELECT name FROM people
JOIN *tablesyouwanttojoin*
WHERE name = "actor number 1"
INTERSECT
SELECT name FROM people
JOIN *tablesyouwanttojoin*
WHERE name = "actor number 2"

This was the most elegant suggestion I could find online since I had the same problem...