CODEWITHSUNDEEP
mysqlsql
HerrimanCoder
HerrimanCoder

Reputation: 7218

Exclude related records - guilt by association

I have this MySQL query:

select * 
from Movies_Genres 
where MovieID = 271

That returns the following recordset:

ID    MovieID  GenreID
======================
924   271      8
1251  271      13

Movies_Genres is a union/junction table with many-many relationships.

Now if I run this slightly modified query:

select * 
from Movies_Genres 
where MovieID = 271
and GenreID <> 13

I get the following recordset:

ID    MovieID  GenreID
======================
924   271      8

So far so good. But what I'm trying to achieve is to have 0 records returned for that 2nd query if the movie has a related GenreID of 13. So, the fact that in my query above the movie has related genres of 13 AND 8, I want 0 records returned because the movie has a GenreID of 13. If it had a GenreID of, say, 8, 1, and 2 - then I would want 3 records returned. If it had a GenreID of only 13, I would want 0 records returned -- which it already does correctly. To summarize: Any movie with a related GenreID of 13 should never return any records, even if it also has other linked GenreIDs.

How can this be achieved?

Upvotes: 1

Views: 44

Answers (3)

Himanshu
Himanshu

Reputation: 3970

Perhaps you need a group by with having

        select * 
          from Movies_Genres 
          group by MovieId having count(GenreID=13) =0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

If you want the original records, then not exists seems appropriate:

select mg.* 
from Movies_Genres mg
where not exists (select 1
                  from movies_genres mg2
                  where mg2.MovieID = mg.MovieID and 
                        mg2.GenreID = 13
                 );

If you just want the movies rather than the genreID details, you can use group by:

select mg.MovieID
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;

In fact, you can add the genre ids as a list in this case:

select mg.MovieID, group_concat(mg.GenreID) as genreids
from Movies_Genres mg
group by mg.MovieID
having sum( mg2.GenreID = 13 ) = 0;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

One option involves aggregation:

SELECT MovieID
FROM Movies_Genres
WHERE MovieID = 271
GROUP BY MovieID
HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) = 0;

The above query is somewhat trivial, because it would at most return a single MovieID, 271, if it did not have the 13 genre associated with it. If you want to find the full records, we can try:

SELECT mg.*
FROM Movies_Genres mg
WHERE mg.MovieID NOT IN (SELECT MovieID FROM Movies_Genres GROUP BY MovieID
                         HAVING COUNT(CASE WHEN GenreID = 13 THEN 1 END) > 0);

Upvotes: 0

Related Questions