On Aug 8, 2014, at 8:35 PM, Errol Emden <[email protected]> wrote:
> I am to list the film title and the leading actor for all of the films
> 'Julie Andrews' played in.
And another one, for diversity’s sake…
Assuming a slightly different data model:
with
AndrewsMovie
as
(
select movie_cast.movie_id
from movie_cast
join person
on person.id = movie_cast.person_id
where person.name = 'Andrews, Julie (I)'
)
select movie.year,
movie.title,
person.name,
movie_cast.attribute
from movie_cast
join movie
on movie.id = movie_cast.movie_id
join person
on person.id = movie_cast.person_id
where movie_cast.attribute like '%<1>'
and exists
(
select 1
from AndrewsMovie
where AndrewsMovie.movie_id = movie_cast.movie_id
)
order by movie.year,
movie.title;
Sample for 2010:
2010|"20 to 1" (2005) {Our All Time Favourite Films (#9.11)}|Newton,
Bert|[Himself - Host] <1>
2010|"Breakfast" (2000) {(2010-05-24)}|Stayt, Charlie|[Himself - Presenter] <1>
2010|"The Daily Show" (1996) {Julie Andrews (#15.12)}|Stewart, Jon (I)|[Himself
- Host] <1>
2010|"The Late Late Show with Craig Ferguson" (2005) {(#6.80)}|Ferguson, Craig
(I)|[Himself - Host] <1>
2010|"The Oprah Winfrey Show" (1986) {(2010-10-28)}|Winfrey, Oprah|[Herself -
Host] <1>
2010|Despicable Me (2010)|Carell, Steve|(voice) [Gru] <1>
2010|Shrek Forever After (2010)|Myers, Mike (I)|(voice) [Shrek] <1>
2010|Tooth Fairy (2010/I)|Johnson, Dwayne (I)|[Derek] <1>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
