Avoid select data from other tables-CodePudding

I'm coding a movies web app, for showing some tags I'm trying to write a query that returns the movie name and the count of how many categorys has assigned it. I'm trying to add a filter that for example: if X movie contains a "Comedy" category this movie doesn't even need to be consider in my query.

At the moment this is the query that I have:

SELECT A.*, B.*, C.* -- A.name, count(C.name) [Categories]  
FROM movies A
INNER JOIN moviesGenres B ON A.id = B.movieId
INNER JOIN genres C ON B.genreId = C.id
WHERE C.name <> 'Comedy'
-- group by A.name, C.name
-- having count(C.name) > 2

At the moment this query is working to return the expected output. But if you run that query with the data in this SQL Fiddle you'll see that is considering the movie "Bad Boys" but this movie has assigned one "Comedy" category so any data from this movie should't be considered.

CodePudding user response：

You need to put that condition in the having clause wich is the where clause of a group.

SELECT m.name, count(g.name) [Genres]  
FROM movies m
INNER JOIN moviesGenres mg ON m.id = mg.movieId
INNER JOIN genres g ON mg.genreId = g.id
GROUP BY m.name
HAVING sum(case when g.name = 'Comedy' then 1 else 0 end) = 0

CodePudding user response：

If you really want to select columns from [moviesGenres] and [genres], go for juergen's answer. If you don't need them, anti join them:

SELECT A.*, B.*, C.* -- A.name, count(C.name) [Categories]  
FROM 
    movies A
WHERE NOT EXISTS
(
    SELECT 1
    FROM 
        moviesGenres Bneg
        inner join genres Cneg ON Bneg.genreId = Cneg.id
    WHERE 
        A.id = Bneg.movieId
        and Cneg.name = 'Comedy'
)