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.
My attempt at a solution, produces all the movie titles but the actors' names
are all for Julie Andrews:
titlename10Julie AndrewsDarling LiliJulie AndrewsDespicable MeJulie AndrewsDuet
for OneJulie AndrewsHawaiiJulie AndrewsLittle Miss MarkerJulie AndrewsMary
PoppinsJulie AndrewsRelative ValuesJulie AndrewsS.O.B.Julie AndrewsShrek the
ThirdJulie AndrewsStar!Julie AndrewsThe Americanization of EmilyJulie
AndrewsThe Pink Panther Strikes AgainJulie AndrewsThe Princess DiariesJulie
AndrewsThe Princess Diaries 2: Royal EngagementJulie AndrewsThe Sound of
MusicJulie AndrewsThe Tamarind SeedJulie AndrewsThoroughly Modern MillieJulie
AndrewsTooth FairyJulie AndrewsTorn CurtainJulie AndrewsVictor VictoriaJulie
Andrews
The SQL code for the above is given below:
SELECT distinct m1.title, a1.name FROM casting c1
JOIN movie m1 on m1.id=c1.movieid
JOIN actor a1 on a1.id=c1.actorid
WHERE (a1.name='Julie Andrews' and c1.ord=1) or ('Julie Andrews' IN (
SELECT a2.name FROM actor a2
WHERE c1.ord<>1 and a2.id=a1.id));
Why don't I get the name of the star Dudley Moore for 10, Steve Carrell for
Despicable Me, etc.? I should appreciate your help in finding the appropriate
solution. Thanks and be well.
Errol Emden
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
