add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `tv_genres` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
PRIMARY KEY (`id`),
INDEX idx_name (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

INSERT INTO `tv_genres` VALUES
(1,'Drama'),(2,'Mystery'),(3,'Adventure'),(4,'Fantasy'),
(5,'Comedy'),(6,'Crime'),(7,'Suspense'),(8,'Thriller');

CREATE TABLE `tv_shows` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(256) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `tv_shows` VALUES
(1,'House'),(2,'Game of Thrones'),(3,'The Big Bang Theory'),(4,'New Girl'),(5,'Silicon Valley'),
(6,'Breaking Bad'),(7,'Better Call Saul'),(8,'Dexter'),(9,'Homeland'),(10,'The Last Man on Earth');

CREATE TABLE `tv_show_genres` (
`show_id` int(11) NOT NULL,
`genre_id` int(11) NOT NULL,
PRIMARY KEY (show_id, genre_id),
INDEX idx_genre_show (genre_id, show_id),
CONSTRAINT `tv_show_genres_ibfk_1` FOREIGN KEY (`show_id`) REFERENCES `tv_shows` (`id`),
CONSTRAINT `tv_show_genres_ibfk_2` FOREIGN KEY (`genre_id`) REFERENCES `tv_genres` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tv_show_genres` VALUES
(1,1),(1,2),(2,3),(2,1),(2,4),(3,5),(4,5),(5,5),(6,6),(6,1),
(6,7),(6,8),(8,6),(8,1),(8,2),(8,7),(8,8),(10,5),(10,1);
Records: 8  Duplicates: 0  Warnings: 0

Records: 10  Duplicates: 0  Warnings: 0

Records: 19  Duplicates: 0  Warnings: 0
/*
* The logic is wrong. This is returnig all titles that have a genre other
* than Comedy, instead of titles that do not have the genre Comedy.
*/
SELECT DISTINCT `title`
FROM `tv_shows` AS t
LEFT JOIN `tv_show_genres` AS s
ON s.`show_id` = t.`id`

LEFT JOIN `tv_genres` AS g
ON g.`id` = s.`genre_id`
WHERE g.`name` != "Comedy";
title
Game of Thrones
Dexter
Breaking Bad
The Last Man on Earth
House
/* This works but does more work than is necessary */
-- EXPLAIN
SELECT DISTINCT `title`
FROM `tv_shows` AS t
LEFT JOIN `tv_show_genres` AS s
ON s.`show_id` = t.`id`

LEFT JOIN `tv_genres` AS g
ON g.`id` = s.`genre_id`
WHERE t.`title` NOT IN
(SELECT `title`
FROM `tv_shows` AS t
INNER JOIN `tv_show_genres` AS s
ON s.`show_id` = t.`id`

INNER JOIN `tv_genres` AS g
ON g.`id` = s.`genre_id`
WHERE g.`name` = 'Comedy')
ORDER BY `title`;
title
Better Call Saul
Breaking Bad
Dexter
Game of Thrones
Homeland
House
/* This is essentially the same query as above but with the unnecessary joins removed */
-- EXPLAIN
SELECT `title`
FROM `tv_shows` AS t
WHERE t.`id` NOT IN (
SELECT `show_id`
FROM `tv_show_genres` AS s
INNER JOIN `tv_genres` AS g
ON g.`id` = s.`genre_id`
WHERE g.`name` = 'Comedy'
)
ORDER BY `title`;
title
Better Call Saul
Breaking Bad
Dexter
Game of Thrones
Homeland
House
-- EXPLAIN
SELECT `title`
FROM `tv_shows` AS t
LEFT JOIN `tv_show_genres` AS s
ON s.`show_id` = t.`id`
AND s.`genre_id` = (SELECT id FROM `tv_genres` WHERE `name` = 'Comedy')
WHERE s.`genre_id` IS NULL
ORDER BY `title`;
title
Better Call Saul
Breaking Bad
Dexter
Game of Thrones
Homeland
House
-- EXPLAIN
SELECT *
FROM tv_shows s
WHERE NOT EXISTS (
SELECT 1
FROM tv_show_genres sg
JOIN tv_genres g ON sg.genre_id = g.id
WHERE sg.show_id = s.id
AND g.name = 'Comedy'
)
ORDER BY title;
id title
7 Better Call Saul
6 Breaking Bad
8 Dexter
2 Game of Thrones
9 Homeland
1 House