Can't get my query to work 1

[whoschad]

Hello,

I've got a personal movie database I'm finishing up with and I can't figure out how to query across multiple linked tables (I'm self taught).

Here's what I want to know:
List all movies in the database that are computer animation, but are NOT Pixar.

Here's the relevant table information:

Movies
MovieID
Title

linkGenres
MovieID
GenreID

Genres
GenreID
Genre

linkSeries
MovieID
SeriesID

Series
SeriesID
Series

"Computer Animation" is one of my Genres (a movie can have multiple genres) and "Pixar" is one my Series (a movie can have multiple series)

I must be writing the SQL wrong when I do it because I can never get this kind of query to work without having to resort to using two separate queries. I'd like to have it all done in one.

Thanks.

 

[nicsin]

could you post here what you've already got so we can help you sort it out?

N

 

[whoschad]

Sure, here's my two queries:

First: 'OnlyComputerAnimation' (to isolate computer animation movies

SELECT Movies.MovieID, Movies.Title
FROM Genres INNER JOIN (Movies INNER JOIN lnkGenres ON Movies.MovieID=lnkGenres.MovieID) ON Genres.GenreID=lnkGenres.GenreID
WHERE Genres.Genre="Computer Animation";

Second: 'AnimationNotPixar' (to remove Pixar from the previous query)

SELECT OnlyComputerAnimation.Title
FROM OnlyComputerAnimation
WHERE OnlyComputerAnimation.MovieID NOT IN (SELECT Movies.MovieID
FROM Series INNER JOIN (Movies INNER JOIN lnkSeries ON Movies.MovieID = lnkSeries.MovieID) ON Series.SeriesID = lnkSeries.SeriesID
WHERE Series.Series="Pixar");

This works fine, but whenever I try to consolidate the two, I get nothing but errors. Isn't there some way to just use one query for this kind of thing?

Thanks again.

 

[traingamer]

What about (not tested)

SELECT Movies.MovieID, Movies.Title
FROM Genres INNER JOIN (Movies INNER JOIN lnkGenres ON Movies.MovieID=lnkGenres.MovieID) ON Genres.GenreID=lnkGenres.GenreID
WHERE Genres.Genre="Computer Animation" [COLOR=blue] AND 
Movies.MovieID NOT IN (SELECT M1.MovieID
FROM Series INNER JOIN (Movies M1 INNER JOIN lnkSeries ON M1.MovieID = lnkSeries.MovieID) ON Series.SeriesID = lnkSeries.SeriesID
WHERE Series.Series="Pixar")[/color];

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[whoschad]

Works perfectly.