Hi,
I was hoping someone can help me out with this sql concept. If i have a many to many relationship - i.e. "actors" and "movies" - then the link table is naturally the "cast" which includes pairs of "actor_id" and "movie_id"

obviously it would be easy to retrieve all the movies based on an actor_id, and all the actors based on a movie_id

here's the questions:
what if i wanted to retrieve all the movies for which 2 actors are in? Is there a single SQL query that i can run, or does this have to be done in separate parts?

the only way i can think of is something like:
select movie_id from cast where actor_id=1 or actor_id=2

then take the result and for each, see which of those movies both actors are in the cast... but that's not very efficient. I want to be able to in theory select many actors and retrieve the set of movie_id 's that are common. And vice versa select many movies and see which cast members (actors) are common

perhaps this is easy and i'm just having a brain-freeze!??

thanks for your help.

wow - thanks! that is a world of help

haven't tried it yet, but looks like you know what you're talkin about

question related to this query:
if i have 3 actors " WHERE actor_id IN ( 1 , 2 , 3 )", and i set "HAVING COUNT(*) = 2" is it possible to return which 2 ID's are used for each row?

i.e. for each row is the GROUP 1,2 or 2,3 or 1,3 ...

thanks

thanks.. this works, but it only retrieves the movie_id - i need to still retrieve all the data from the movies table..

here's what i had before:

SELECT * FROM cast, movies WHERE cast.movie_id = movies.movie_id AND cast.actor_id IN ( 458,481,187 ) GROUP BY cast.movie_id HAVING COUNT(*) = 2

how would this new method work while still retrieving movies.* ?

thanks again

no, it retrieves the movie_id plus the actor_id...

... as requested

you've now changed the requirements a third time

i think it's time you tried to do it -- come on, it's easy

just add another INNER JOIN to what we've got already

haha - you're right. i like to design on the fly.. can you tell?

ok - i'll give it another shot - whatever i did last time caused a massive operation and timed out...

Try this it can help you achieve your goal:

SELECT movies.*, GROUP_CONCAT(DISTINCT cast.actor_id ORDER BY cast.actor_id SEPARATOR ', ') as actors FROM cast LEFT JOIN movies WHERE cast.movie_id = movies.movie_id AND cast.actor_id IN ( 458,481,187 ) GROUP BY cast.movie_id HAVING COUNT(cast.actor_id) >= 2

aaaaaaaaaaaaaaaarrrrrrgggggghhhhhhhh!!!!

SELECT movies.*, GROUP_CONCAT(DISTINCT cast.actor_id ORDER BY cast.actor_id SEPARATOR ', ') as actors FROM cast LEFT JOIN movies ON cast.movie_id = movies.movie_id WHERE cast.actor_id IN ( 458,481,187 ) GROUP BY cast.movie_id HAVING COUNT(cast.actor_id) >= 2