列出"哈里森·福特"出演的电影

Question

为什么这个问题的答案不正确？

数据库

movie (id(PK),  title,  yr,     director(FK),   budget,     gross)
actor (id(PK),  name )
casting (movieid(PK, FK),   actorid(PK, FK),    ord)

问题：列出"哈里森·福特"出现的电影(此处为原始链接(

我的答案：

select title
from movie
were id IN
(
select movieid as id
from casting
where actorid IN 
(
select id as actorid
from actor
where name = 'Harrison Ford'
)
) X

Answer 1

更正were->where并删除尾部X后，语法和结果正确。

select title
from movie
where id IN
(
select movieid as id
from casting
where actorid IN 
(
select id as actorid
from actor
where name = 'Harrison Ford'
)
)

错误消息表明它正在使用MariaDB

Answer 2

select title from movie inner join casting on (movie.id = casting.movieid)
inner join actor on (casting.actorid = actor.id)
where actor.name = 'Harrison Ford'

请使用上面的SQL代码来回答SQL动物园上的这个问题；它在练习中与"；更多联接"；。因此，你应该使用Joins来解决

Answer 3

我使用CTE和JOIN:的解决方案

-- find the movieid in which Harrison Ford played
WITH t AS (
SELECT DISTINCT movieid
FROM actor
JOIN casting ON actor.id = casting.actorid
WHERE name = 'Harrison Ford'
)
-- Get the movie title using a simple inner join
SELECT title
FROM movie
JOIN t ON movie.id = t.movieid