Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006

    Unanswered: directors, actors, films (was "SqlPlus query. Need some help")

    What i want to find is the following:

    Find first and lastname for all female directors who have directed more than 5 movies,
    and have used the same actor in all of their movies.

    I've done the first part of this query which lists female directors with more than 5 movies:

    select firstname, lastname
    from person
    where gender = 'F' AND personid in
    (select personid
    from participation
    where partname = 'director'
    group by personid
    having count(personId)>5)
    order by surname;

    Now, how do I find the directors that have used the same actor in all of their movies?

    Click image for larger version. 

Name:	db1.jpg 
Views:	35 
Size:	14.0 KB 
ID:	5957
    Last edited by dbnewbee; 04-23-06 at 20:28.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada

    how do you define an actor? and when you say "have used the same actor in all of their movies" does this mean any actor or all actors?

    by the way, you shouldn't have participation_id as the primary key of Participation -- the primary key should consist of the pair of columns person_id, film_id | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2006
    1. an actor is defined to participate in a film whenever partname = 'actor'.

    2. Any actor is sufficient

Posting Permissions

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