2

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,

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

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

4
  • 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 '20 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 '20 at 23:18
  • @saquiel hint added to the answer. Jan 26 '20 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 '20 at 12:34
4

I've found GROUP BY to be very useful:

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

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

1
  • Thanks Augusto, I think the GROUPBY of mishka is a bit more readable.
    – saquiel
    Jan 20 at 7:57

You must log in to answer this question.

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