On Aug 8, 2014, at 8:35 PM, Errol Emden <eem...@hotmail.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users