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

ah yes, good old mysql, no subqueries before version 4.1

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

easy -- change the left outer joins to inner, and drop the GROUP BY and HAVING clauses

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

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