Getting specific in an INNER JOIN mysqli select statement with group_concat

I have a table similar to below, I am trying to have a mysqli_query to get the movies that do not have the genre ‘Love’.

create table movies (movieid int(1), title varchar(20),
                     plot varchar(20), rating int(2),
                     director  varchar(20));
create table genres (genreid int(1), genre varchar(20));
create table moviegenres (moviegenresid int(1), movieid int(1),
                          genreid int(1));

insert into movies values
(1, 'movie1', '',   10 , '');
(2, 'movie2', '',   10 , '');
(3, 'movie3', '',   10 , '');
(3, 'movie4', '',   10 , '');
insert into genres values
(1, 'Horror'),
(2, 'Thriller'),
(3, 'Action'),
(4, 'Love');
insert into moviegenres values
(1, 1, 2),
(2, 1, 3);
(3, 1, 4),
(4, 2, 2);
(5, 2, 3);

Using this query I can get the movies in a specific genre like ‘Action’

select m.*,group_concat(g.genre)
from movies m inner join moviegenres mg
on m.movieid=mg.movieid
inner join genres g
on g.genreid=mg.genreid
where g.genre = 'Action'
group by m.movieid
;

But If I do a query like this in order to get the result I want, getting the movies that do not have the genre ‘Love’.

select m.*,group_concat(g.genre)
from movies m inner join moviegenres mg
on m.movieid=mg.movieid
inner join genres g
on g.genreid=mg.genreid
where g.genre != 'Love'
group by m.movieid
;

I still have ‘movie1’ and ‘movie2’ in the result, however I’m trying to get only ‘movie2’ because that is the only movie that does not have ‘Love’ in its genres.

Answer

Remove the where and use a having:

having sum(g.genre = 'Love') = 0 and
       sum(g.genre = 'Action') > 0

Advertisement

Answer