Ugh ok I'm terrible at explaining things, so I'll just give you the quotes and links first:
Problem 4b (near bottom):
4b. List the film title and the leading actor for all of 'Julie Andrews' films.
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
(Note: movie.id = casting.movieid, actor.id = casting.actorid)
My answer (doesn't work):
SELECT title, name
FROM casting JOIN movie
ON casting.movieid = movie.id
JOIN actor
ON casting.actorid = actor.id
WHERE name = 'Julie Andrews'
AND ord = 1
The problem here is that it wants the list of lead actors of movies with 'Julie Andrews' as an actor (who is not necessarily the lead actor), but all I'm doing with my answer is getting the movies where she is the lead (ord = 1).
How do I specify the list of lead actors without 'Julie Andrews' being it? I suspect I have to do something with GROUP BY, but I can't figure out what at the moment...
Edit: Do I need to use a nested SELECT?