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 ;-)ah yes, good old mysql, no subqueries before version 4.1

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

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