Как вывести данные?

Question

[kuznecov85]

Есть три таблицы:
films
filmsjanrs
janr

Т.е. фильмы и жанры. Как пример запроса, надо вывести все фильмы ужасы, но НЕ комедии.
Запрос

SELECT * 
FROM `films`
	left join `filmsjanrs` 
		left join `janr` on `filmsjanrs`.janrid = `janr`.id and `janr`.id <> 2
	on `films`.id = `filmsjanrs`.filmid 
	
WHERE `janr`.id = 1

Возвращает фильмы, которые относятся к жанру ужасы. Но среди них есть фильм, который одновременно относиться и к жанру комедии. Как правильно написать запрос?

Заранее большое спасибо =)

Структура таблиц:

CREATE TABLE IF NOT EXISTS `films` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

--
-- Дамп данных таблицы `films`
--

INSERT INTO `films` (`id`, `name`) VALUES
(1, 'Самый лучший фильм'),
(2, 'Пила 2'),
(3, 'Пила 3'),
(4, '18 мгновений весны'),
(5, 'ФильмМистика'),
(6, 'Американский пирог');

-- --------------------------------------------------------

--
-- Структура таблицы `filmsjanrs`
--

CREATE TABLE IF NOT EXISTS `filmsjanrs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filmid` int(11) NOT NULL,
  `janrid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Дамп данных таблицы `filmsjanrs`
--

INSERT INTO `filmsjanrs` (`id`, `filmid`, `janrid`) VALUES
(1, 1, 2),
(2, 2, 1),
(3, 3, 1),
(4, 4, 3),
(5, 5, 4),
(6, 6, 2),
(7, 1, 1),
(8, 1, 6),
(9, 2, 8),
(10, 2, 6);

-- --------------------------------------------------------

--
-- Структура таблицы `janr`
--

CREATE TABLE IF NOT EXISTS `janr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ;

--
-- Дамп данных таблицы `janr`
--

INSERT INTO `janr` (`id`, `name`) VALUES
(1, 'Ужасы'),
(2, 'Комедия'),
(3, 'Мелодрама'),
(4, 'Мистика'),
(5, 'Детское кино'),
(6, 'Боевик'),
(7, 'Боевые исскуства'),
(8, 'Детектив'),
(9, 'Иторичексиий'),
(10, 'Фантастика'),
(11, 'Повседневность'),
(12, 'Приключения'),
(13, 'Психология'),
(14, 'Сверхестественное'),
(15, 'Фентези');

Comments

[longclaps]

долбаный русглиш

[kuznecov85]

longclaps, согласен, не корректное название. По-хорошему таблицу films надо было назвать film, но на синтаксис SQL это не повлияет. Поэтому вопрос остается открытым :)

[longclaps]

kuznecov85, по-хорошему - movie.
А влияет это на моё восприятие, я вас начинаю воспринимать как неряху, и брезгую отвечать на вопрос по существу.

[kuznecov85]

longclaps, с move не могу не согласиться. Обещаю исправиться и скорректировать название таблиц. Если все-таки поможете с моим вопросом, моя благодарность не будет знать границ :)

Answer 1

[longclaps]

SELECT movie.*
FROM movie
  JOIN movie_genre A ON movie.id = A.movie_id
  JOIN movie_genre B ON movie.id = B.movie_id
WHERE A.genre_name = 'horror' AND B.genre_name = 'comedy';

Comments

[kuznecov85]

Я правильно понимаю что вы предлагаете мне отказаться от связующей таблицы filmsjanrs, в которой у меня хранятся ключи?

Если бы у меня было две таблицы, решение конечно упрощается. И условие вывода списка фильмов такое:
Надо вывести все фильмы, которые относятся к жанру Ужасы, но при этом они не относятся к жанру комедия.

В моем примере есть фильм который относится и к комедии, и к ужасам. Так вот он в результате запроса не должен выводиться

[kuznecov85]

Разбираясь с этой задачей, прихожу к выводу что она близка к невозможной :)

[longclaps]

kuznecov85, 3 таблицы, movie <-> movie_genre <-> genre.
Текстовое поле genre_name вставил просто для наглядности, вместо
A.genre_id = 1 AND B.genre_id = 2

[kuznecov85]

longclaps, запрос

SELECT M.*
FROM movie M
   JOIN movie_genre A ON M.id = A.movie_id
   JOIN movie_genre B ON M.id = B.movie_id
WHERE A.genre_id=1 AND B.genre_id=2

Мне выводит фильмы, которые и комедия , и ужасы. Если я пишу

SELECT M.*
FROM movie M
   JOIN movie_genre A ON M.id = A.movie_id
   JOIN movie_genre B ON M.id = B.movie_id
WHERE A.genre_id=1 AND B.genre_id<>2

Тогда у меня выводятся все ужасы (включая тот фильм, который одновременно и комедия).
Такое чувство, что чего-то не хватает в запросе:)

[longclaps]

kuznecov85,

SELECT movie.*
FROM movie
  JOIN movie_genre ON movie.id = movie_genre.movie_id
WHERE movie_genre.genre_id = 1 AND id NOT IN (
  SELECT movie.id
  FROM movie
    JOIN movie_genre ON movie.id = movie_genre.movie_id
  WHERE movie_genre.genre_id = 2);

[kuznecov85]

longclaps, огромное человеческое спасибо :)

Answer 2

[Rsa97]

SELECT *
  FROM `films` AS `f`
  JOIN `filmsjanrs` AS `fj1` ON `fj1`.`filmid` = `f`.`id`
    AND `fj1`.`janrid` IN (SELECT `id` FROM `janr` WHERE `name` = 'Ужасы')
  LEFT JOIN `filmsjanrs` AS `fj2` ON `fj2`.`filmid` = `f`.`id`
    AND `fj2`.`janrid` IN (SELECT `id` FROM `janr` WHERE `name` = 'Комедия')
  WHERE `fj2`.`filmid` IS NULL

И поле `id` в `filmsjanrs` здесь лишнее, первичным ключом лучше сделать пару (`filmid`, `janrid`)

Comments

[kuznecov85]

У меня этот запрос вывел фильм, который одновременно и комедия, и ужас

[Rsa97]

kuznecov85, Да, не учёл промежуточную таблицу. Поправил.

[kuznecov85]

Rsa97, так работает. Спасибо!
Правда с учетом замечаний longclaps'а, у меня такой код:

SELECT *
  FROM `movie` AS `f`
  JOIN `movie_genre` AS `fj1` ON `fj1`.`movie_id` = `f`.`id`
    AND `fj1`.`genre_id` IN (SELECT `id` FROM `genre` WHERE `name` = 'Ужасы')
  LEFT JOIN `movie_genre` AS `fj2` ON `fj2`.`movie_id` = `f`.`id`
    AND `fj2`.`genre_id` IN (SELECT `id` FROM `genre` WHERE `name` = 'Комедия')
  WHERE `fj2`.`movie_id` IS NULL