this is the first time I use this forum although I already got plenty of help from it!
My problem is the following. I have created a movie database. I am using Oracle iSQL*Plus.
One table is called 'movie_t' another one 'person_t' and inside this last table is nexted a table called 'castmembers'.
one of my query is : "For all movies,list the leading actress, i.e. the first billed actress (in the order of credits). Show the movie Title, Genre, Director and Name of the actress ".
I found a complex solution that works:
SELECT title, name, creditorder "CREDIT ORDER"
FROM (select RANK() OVER (partition by m.title ORDER BY c.creditorder) rankcredit, m.title, p.name, c.creditorder
FROM person_t p, table(p.castmembers) c, movie_t m
WHERE c.movie_ref = ref(m) and p.gender = 'F') T WHERE RANKCREDIT = 1;
However, I am pretty sure that there must be a simple solution without using a method and partition.
I tried the following statement:
select m.title, m.director.name, m.genre, p.name, c.creditorder
from movie_t m, person_t p, table(p.castmembers)c
where ref(m) = c.movie_ref
and c.creditorder in
from movie_t n, person_t q, table(q.castmembers)d
where m.title=n.title and m.director.name=n.director.name and
m.genre=n.genre and p.name=q.name and p.gender = 'F'
group by n.title, n.director.name, n.genre )
This unfortunately does not work, as it returns me all actresses!
please check my .doc file, it makes it easier to understand!
if anyone could help me a little bit, I would greatly appreciate, I have spent my whole sunday on that and it's getting realy frustrating for me!
SELECT c.movie_ref.title, p.name, c.creditorder, m.genre, m.director.name
FROM movie_t m, person_t p, table(p.castmembers)c
WHERE c.movie_ref= ref(m) and p.gender = 'F' and (c.creditorder) = all
( select min(c.creditorder) from person_t p, table(p.castmembers)c
WHERE c.movie_ref= ref(m) and p.gender = 'F');
if any of u think about anything else let me know, maybe I can also use the "having" function?