题目描述
使用join查询方式找出没有分类的电影id以及名称
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
解题方案
本题的思路在于先使用电影和分类进行left join,然后筛选出的表判断其category_id是否为null,若为null则输出即可。
SELECT
fcc.film_id,
fcc.title
FROM
(
SELECT
*
FROM
film AS f
LEFT JOIN film_category AS fc ON f.film_id = fc.film_id
) AS fcc
WHERE
fcc.category_id IS NULL