牛客SQL练习第30题

题目描述

film表

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));

category表

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));

film_category表

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp);

你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

解答：

子查询：

select f.title,f.description 
from film as f
where f.film_id in (select fc.film_id 
					from film_category as fc 
              		 where fc.category_id in (select c.category_id 
              		 							from category as c
		                                        where c.name = 'Action')); 

非子查询：

select f.title,f.description
from film_category fc,film f ,category c 
where fc.category_id=c.category_id
and fc.film_id=f.film_id 
and c.name='Action'