sqlzoo错题记录

more join operations

movie

id

title

yr

director

budget

gross

 

actor

id

name

 

casting

movieid

actorid

ord

 

12.列出“朱莉·安德鲁斯”出演的所有电影的电影标题和主演

错误

select movie.title ,name 
from movie 
join casting 
on movie.id=casting.movieid 
join actor 
on actor.id=casting.actorid
where movie.id in(SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')) 

正确答案

select movie.title ,name 
from movie 
join casting 
on movie.id=casting.movieid and ord=1
join actor 
on actor.id=casting.actorid
where movie.id in(SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews')) 

原因没有加主演 ord=1