On 8 Aug 2014, at 7:35pm, 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. The following is structure of the tables used: > movie(id, title, yr, director, budget, gross); > actor(id, name); > casting(movieid, actorid, ord). The column name ord has a value of 1 if the > actor is in starring role.
All the lead roles of all stars in all movies SELECT leadActor.name, movie.title FROM casting JOIN actor AS leadActor ON leadActor.id = casting.actorid JOIN movie ON movie.id = casting.movieid WHERE casting.ord = 1 All the lead roles of all stars in all movies Julie Andrews has been in: SELECT leadActor.name, movie.title FROM casting JOIN actor AS leadActor ON leadActor.id = casting.actorid JOIN movie ON movie.id = casting.movieid JOIN actor AS selectedActor ON selectedActor.id = casting.actorid WHERE casting.ord = 1 AND selectedActor.name = 'Julie Andrews' If would be more efficient to find out Julie's actorid first, and use that for filtering instead of her name. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
