Hi everyone!

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
(Select min(d.creditorder)
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!

thanks all,
Gaetan

sorry guys, my file did not atached, I try one more time :-)

i finally found the solution :-)

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?

gaetan :-)