again new query. i am trying to solve this from one hour. please help

Question

Query : List the film title and the leading actor for all of 'Julie Andrews' films.

there are three tables

movie (id, title, yr, score, votes, director)

actor (id, name)

casting (movieid, actorid, ord)

select movie.title,actor.name as cont
from movie
join casting on (movie.id=casting.movieid)
join actor on (casting.actorid=actor.id)
where actor.name='Julie andrews'

actually i can't get how to find the leading actor.

Answer 1

Firstly, you shouldn't use id for the key column, use movieid and actorid. This makes joins easier:

select movie.title, lead.name as cont
  from actor
  join casting             using (actorid)
  join movie               using (movieid)
  join casting as leadrole using (movieid)
  join actor   as lead     on (leadrole.actorid = lead.actorid)
 where actor.name = 'Julie andrews'
   and leadrole.ord = 1

This assumes that the lead actor's ord column is 1. It is also completely untested, so you will probably need to debug it.

Answer 2

Assuming the lead role is ord = 1 you can try this out (I didn't test it)

EDIT:

select 
      M.title, 
      (select 
            A1.name 
       from 
            casting CI
       join
            actors A1
       on
            A1.id = CI.actorid      
       where 
            CI.movieid = M.id 
       and 
            CI.ord = 1) as LeadingActor 
from 
      movie M
join 
      casting C
on 
      M.id = C.movieid
join 
      actor A 
on 
      C.actorid = A.id
where 
      A.name='Julie andrews'

Answer 3

You almost for sure will need a sub query to achieve this one way or another.

select movie.title, actor.name
from movie
inner join casting 
    on movie.id = casting.movieid
    and casting.ord = 1 --Assuming this is the cast order flag
inner join actor
    on casting.actorid = actor.id
where movie.id in (
    select movie.id
    from movie 
    inner join casting 
        on movie.id = casting.movieid
        and casting.actorid = {your actorID in this case Julie Andrew's id
 )

Disclaimer: I didn't test this as you didn't provide an easy way to do so.

If you really wanted to go by name (which could lead to false positive) then you can add an extra join to the in clause sub query.