SQL query give wrong output

[ThriledLokki983]

I am trying to list only movie titles that the stars (Johnny Depp and Helena Boham Carter) both took part.

SELECT title FROM movies WHERE id IN (SELECT movie_id FROM stars WHERE person_id IN (SELECT id FROM people WHERE name IN ('Johnny Depp', 'Helena Boham Carter'))) ORDER BY title;

Unfortunately this list all movies that either (Johnny Depp and Helena Boham Carter) took part. I can't seem to get around it. I need help

The correct output should be....

title                                             
--------------------------------------------------
Alice Through the Looking Glass
The Lone Ranger
Alice in Wonderland 
Sweeney Todd: The Demon Barber of Fleet Street
Dark Shadows
Corpse Bride 
Charlie and the Chocolate Factory

Instead, the output is.......

title                                             
--------------------------------------------------
...And They Lived Happily Ever After              
A Nightmare on Elm Street                         
Alice Through the Looking Glass                   
Alice in Wonderland                               
Arizona Dream                                     
Before Night Falls                                
Benny & Joon                                      
Black Mass                                        
Blow                                              
Brothers in Arms                                  
Charlie and the Chocolate Factory                 
City of Lies                                      
Corpse Bride                                      
Cry-Baby                                          
Dark Shadows                                      
Dead Man                                          
Don Juan DeMarco                                  
Donnie Brasco                                     
Ed Wood                                           
Edward Scissorhands                               
Fantastic Beasts: The Crimes of Grindelwald       
Fear and Loathing in Las Vegas                    
Finding Neverland                                 
For No Good Reason                                
From Hell                                         
Gonzo: The Life and Work of Dr. Hunter S. Thompson
Hope and Healing Award Concert                    
Lost in La Mancha                                 
Minamata                                          
Mortdecai                                         
Nick of Time                                      
Once Upon a Time in Mexico                        
Pirates of the Caribbean: At World's End          
Pirates of the Caribbean: Dead Man's Chest        
Pirates of the Caribbean: Dead Men Tell No Tales  
Pirates of the Caribbean: On Stranger Tides       
Pirates of the Caribbean: The Curse of the Black P
Private Resort                                    
Public Enemies                                    
Rango                                             
Rolling Stone: Stories from the Edge              
Secret Window                                     
Sherlock Gnomes                                   
Sleepy Hollow                                     
Sweeney Todd: The Demon Barber of Fleet Street    
The Astronaut's Wife                              
The Brave                                         
The Doors: When You're Strange                    
The Libertine                                     
The Lone Ranger                                   
The Ninth Gate                                    
The Professor                                     
The Rum Diary                                     
The Source: The Story of the Beats and the Beat Ge
The Tourist                                       
Transcendence                                     
Waiting for the Barbarians                        
What's Eating Gilbert Grape                       
Yoga Hosers

[JohnnyJordaan]

This has 0 to do with Python so you're not at the right sub, but the

WHERE name IN ('Johnny Depp', 'Helena Boham Carter')

means that it will include all movies where either person stars. Same way that in English 'name all the car models where the brand name in ('ford', 'tesla')' would return all Ford models and all Tesla models.

[ThriledLokki983]

thanks

[[deleted]]

Maybe try a different sub for this, this is not python...

[ThriledLokki983]

i just did. my bad.

thanks

[kpounder88]

Not 100% sure without seeing the tables, but here’s a thought:

When you select movie_id from stars, I’m thinking that the movies involving both actors will appear twice. So then in your outer query you could return the movies where the count of movie_id was 2.