SQL Query of Using JOIN

I have this tables

Movie a
id  title           yr    score  votes
--  --------------  ----  -----  -----
1   ‘Harry Potter’  1985  9.98   2
2  开发者_Python百科 ‘Beasdas’       1985  5.28   3

actor b
id  name
--  --------------
1   ‘Keanu Reeves’

casting c
movieid  actorid  ord
-------  -------  ---
1        1        2

I want to List the film title and the leading actor for all of 'Julie Andrews' films. Is this correct?

SELECT title, name
   FROM movie a, actor b, casting c
   WHERE title IN(SELECT title
                  FROM movie a, actor b, casting c
                  WHERE name = 'Julie Andrews' AND
                             a.id = c.movieid  AND
                             b.id = c.actorid) AND
                                         ord = '1' AND
                                         a.id = c.movieid AND
                                         b.id = c.actorid;    

If you want all results where 'Julie Andrews' is the lead actor then this is what you need:

SELECT a.Name, m.Name
FROM Actor a
INNER JOIN Casting AS c ON a.actorid = a.id AND c.ord = 1
INNER JOIN Movie AS m ON m.id = c.movieid
WHERE a.name = 'Julie Andrews'

On the other hand if you need all films where 'Julie Andrews' had a role, but not necessarily being the lead actor then you need the following:

SELECT m.Title, a2.Name 
FROM Movie AS m
INNER JOIN Casting AS c1 ON c1.movieid = m.id
INNER JOIN Actor AS a1 ON a1.id = c1.actorid AND a1.name = 'Julie Andrews'
INNER JOIN Casting AS c2 ON c2.movieid = m.id AND c2.ord = 1 
INNER JOIN Actor AS a2 ON a2.id = c2.actorid

Hope this helps!

If you want Cartesian product it can look like this

SELECT title, name   
FROM movie a, actor b, casting c  
 WHERE --conditions for joining ables   
   a.id = c.movieid  AND    
b.id = c.actorid and
--other conditions    
  b.name = 'Julie Andrews' 
AND    b.ord = '1' 

but joins are better

select title, name
FROM movie a
  join casting c on a.id = c.movieid
  join actor b   on b.id = c.actorid
where b.name = 'Julie Andrews' AND
  b.ord = '1'

Can you please write the expected result? It seems that there are three tables

1.Movie 2.Actor 3.Casting Casting is a joining table that contains combined info of Movie and Actor.