movie db query problems

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,
Gaetansorry 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 :-)sql

MoveFirst re-runs procedure

Okay this has had me stumped for a while... I've now sussed that myRecSet.MoveFirst can re-run the query!! Eh?

My SQL is "EXEC sp_Proc 'YYYYMM'" This proc will create records in a table if they don't exist, and return a msg. If the records already exist it returns a diff msg. Simple!

I create the recordset in the fashion of myRS = myDB.Execute (mySQL) though I've tried myRs.Open mySql, myDB, myRSType (with various recordset types) with the same result.

I run the query and prior to doing a MoveFirst the 1 line recordset has the expected result (create). When I do a MoveFirst the recordset has the 'existing' message.

Now I know the MoveFirst is not actually required (but it's in a lot of our old systems), but I can't see why this should happen. I've got a big dent in me head now from bashing it against the desk. Any comments or ideas would be most welcome before I throttle the soft toy on my desk!Have you tried this with pubs or northwind databases in sql server ? Post your code and sp.