May 31, 2004 at 12:02 pm
I am trying to understand how this works.
I have 3 tables: movie,actor and casting with following columns:
movie
=====
movieid - pk
title
actor
=====
actorid -pk
name
casting
=======
movieid - pk
actorid - pk
ord - 1 or 2 will be lead actors and actresses.
I have the following question:
List the film title and the leading actor for all of 'Julie Andrews' films.
June 1, 2004 at 12:03 am
Try this......
SELECT mm.title, aa.[name]
FROM movie mm INNER JOIN
casting cc ON mm.movieid = cc.movieid INNER JOIN
actor aa ON cc.actorid = aa.actorid
and mm.movieid in(SELECT m.movieid
FROM movie m INNER JOIN
casting c ON
m.movieid = c.movieid INNER JOIN
actor a ON
c.actorid = a.actorid
WHERE (a.[name] = 'Julie Andrews'))
WHERE (cc.ord IN (1, 2))
Andy.
June 1, 2004 at 12:33 am
Thanks. It worked. Is there any difference between a JOIN and an INNER JOIN?
June 1, 2004 at 12:56 am
In T-SQL you have different join types...
(INNER) JOIN
A join that displays only the rows that have a match in both joined tables.
OUTER JOIN - LEFT OUTER / RIGHT OUTER Joins
A join that includes rows even if they do not have related rows in the joined table.
FULL OUTER JOIN
All rows in all joined tables are included, whether they are matched or not.
CROSS JOIN
A join whose result set includes one row for each possible pairing of rows from the two tables.
Andy.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply