The SQL script you wrote actually provides the same information as mine - it
lists all movies that Julie Andrews is in but it does NOT provide who is the
leading actor in each movie, as all names selected is that of Julie Andrews.
> Date: Fri, 8 Aug 2014 20:55:55 +0200
> From: luu...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What am I doing wrong?
>
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
