Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: more than 1 to many relationship

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT movie_id 
      FROM cast 
     WHERE actor_id IN ( 1 , 2 )
    GROUP
        BY movie_id
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    wow - thanks! that is a world of help

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

  4. #4
    Join Date
    Mar 2009
    Posts
    5
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.movie_id 
         , t.actor_id
      FROM (
           SELECT movie_id 
             FROM cast 
            WHERE actor_id IN ( 1 , 2 , 3 )
           GROUP
               BY movie_id
           HAVING COUNT(*) = 2
           ) AS q
    INNER
      JOIN cast AS t
        ON t.movie_id = q.movie_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2009
    Posts
    5
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by yms
    thanks.. this works, but it only retrieves the movie_id
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2009
    Posts
    5
    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...

  9. #9
    Join Date
    Aug 2005
    Posts
    30
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ashish_mat1979
    FROM cast LEFT JOIN movies WHERE cast.movie_id = movies.movie_id
    aaaaaaaaaaaaaaaarrrrrrgggggghhhhhhhh!!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2005
    Posts
    30
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •