exclude SQL with joins

Question

I'm trying to exclude something from my results.

I'm joining 3 tables, 1 with movies, 1 with genres and 1 to combine these 2.

So the results give a row for each movie for each genre.

So If I have 2 movies with each 3 genre's I get 6 results.

Now I want to be able to say I don't want a movie with this specefic genre. If I do this by saying AND tblmoviegenre.ID <> genre.ID I just get 1 row less, but the movie is still in the results, just without that one row for the specefic genre.

Does anyone know how to fix this?

Answer 1

I'm not sure what is what you want. If I get it, this should work fine

select distinct M.IdMovie, M.MovieName --And Other Fields
from Movies M
inner join MoviesPerGenre MPG on M.IdMovie = MPG.IdMovie
inner join Genre G on MPG.IdGenre = G.IdGenre
where M.IdMovie not in (Select IdMovie from MoviesPerGenre  where IdGenre = 58)

replace 58 with the Id Of the Genre you want to filter

Answer 2

You want to exclude a movie, if it has that genre, even if it also is in other genres which are not excluded?

select <whatever>
from movie, genre, moviegenre
where <join conditions>
and not exists (select 'x' from moviegenre where movie.id = moviegenre.id and moviegenre = "some genere you want to exclude)

Answer 3

Lets say that your third table is

    | movie | genre
---------- -------------
       1    |   2
       2    |   2
       3    |   3
       3    |   2
       4    |   1

and now you want to exclude all with genre 2 then you can write

SELECT movie.name,genre.name 
FROM   movie inner join combine_info on movie.id = combine_info.movie, 
       genre inner join combine_info on genre.id = combine_info.movie
WHERE  movie.id NOT IN(SELECT movie 
                           FROM   combine_info 
                           WHERE  genre = 2);