On 8-8-2014 20:35, Errol Emden 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. 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
This should list all movies with 'Julie Andrews' in a role (starring, or not): 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' Can you explain why you added the part that i erased from the query? Because, basically adding 'she should be starring' (ord=1) or 'she should have a role not starring' (c1.ord<>1 and a2.id=a1.id) will make things unnecessarily complicated. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users