Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Angry Unanswered: NB: query troubles

    I'm making a movie site.

    I have 3 tables: actors, movies, mov_act
    mov_act is the link tables bacause of the many-to-many relationship.

    i need to find the actors that aren't in a specified movie(id=2 for instance).

    simple for you guys, hard for a newbie.

    any help?

    thanks.

    btw: not subqueries usage please, because can't upgrade my isp's SQL version ;-)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah yes, good old mysql, no subqueries before version 4.1

    Code:
    select actors.name
      from actors
    left outer
      join mov_act
        on actors.id = mov_act.actorid
    left outer
      join movies
        on mov_act.movieid = movies.id
       and                   movies.name = 'ben hur'
    group
        by actors.name
    having count(movies.name) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    thanks you this worked

    select mvs_actors.fname
    from mvs_actors
    left outer
    join mvs_mov_act
    on mvs_actors.id = mvs_mov_act.act_id
    left outer
    join mvs_movies
    on mvs_mov_act.mov_id = mvs_movies.id
    and mvs_movies.id = 1
    group
    by mvs_actors.fname
    having count(mvs_movies.id) = 0

    i now have all actors that aren't in movie with id=1

    stupid question, but how do i get the once that are in that movie with id=1??

    thank you very much

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do i get the ones that are in that movie with id=1??
    easy -- change the left outer joins to inner, and drop the GROUP BY and HAVING clauses

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, if you already (somehow) know what the id of the desired movie is, there's no need to join from the mvs_mov_act to the mvs_movies table

    actors in the movie id=1 --

    select mvs_actors.fname
    from mvs_actors
    inner
    join mvs_mov_act
    on mvs_actors.id = mvs_mov_act.act_id
    and mvs_mov_act.mov_id = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Posts
    3
    Thanks very much.

    These are the two Queries that work.
    The first one seems a bit long(in comparison to the second), but it works



    $query = "select mvs_actors.fname,mvs_actors.lname,mvs_actors.id ";
    $query .= "from mvs_actors ";
    $query .= "left outer ";
    $query .= "join mvs_mov_act ";
    $query .= "on mvs_actors.id = mvs_mov_act.act_id ";
    $query .= "left outer ";
    $query .= "join mvs_movies ";
    $query .= "on mvs_mov_act.mov_id = mvs_movies.id ";
    $query .= "and mvs_movies.id = ".$recordID." ";
    $query .= "group by mvs_actors.fname ";
    $query .= "having count(mvs_movies.id) = 0 ";





    $query = "select mvs_actors.fname,mvs_actors.lname,mvs_actors.id ";
    $query .= "from mvs_actors ";
    $query .= "inner ";
    $query .= "join mvs_mov_act ";
    $query .= "on mvs_actors.id = mvs_mov_act.act_id ";
    $query .= "and mvs_mov_act.mov_id = ".$recordID." ";

Posting Permissions

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