Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Sydney
    Posts
    3

    Red face Unanswered: 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,
    Gaetan

  2. #2
    Join Date
    May 2003
    Location
    Sydney
    Posts
    3

    Re: movie db query problems

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

  3. #3
    Join Date
    May 2003
    Location
    Sydney
    Posts
    3

    Re: movie db query problems

    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 :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •