1

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,

  • Find my way with a final test : HAVING COUNT(DISTINCT people.name) = 2. Thanks for your answers. – saquiel Jan 27 at 20:08
  • il try this one out as well – rassenguy Jan 28 at 13:31
6

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

| improve this answer | |
  • Thanks, but the OR select films with the 1st star and films with the 2nd star. I need the films were both stars are in. – saquiel Jan 25 at 22:18
  • struggling with the same problem, getting all of the movies that each of these individuals starred in is doable with, but finding all of the movies where both individuals where stars is harders – rassenguy Jan 25 at 23:18
  • @saquiel hint added to the answer. – DinoCoderSaurus Jan 26 at 17:47
  • 1
    @saquiel i found an operator that helps out a lot after you managed to create a list of each both stars individually, it is the INTERSECTsql operator, note that this operator is not mentioned in w3 sql reference @DinoCoderSaurus thanks for giving mentioning the word intersection, helped alot :) – rassenguy Jan 27 at 12:34
3

I've found GROUP BY to be very useful:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .