CODEWITHSUNDEEP
sqloracle-databaseselect
s0re
s0re

Reputation: 35

Oracle sql - referencing tables

My school task was to get names from my movie database actors which play in movies with highest ratings I made it this way and it works : 

select name,surname 
from   actor 
where  ACTORID in(
  select actorid 
  from   actor_movie
  where  MOVIEID in (
    select movieid
    from   movie
    where  RATINGID in (
      select ratingid
      from   rating
      where  PERCENT_CSFD = (
        select max(percent_csfd)
        from   rating
      )
    )
  )
);

the output is :

Gary Oldman
Sigourney Weaver

...but I'd like to also add to this select mentioned movie and its rating. It accessible in inner selects but I don't know how to join it with outer select in which i can work just with rows found in Actor Table. Thank you for your answers.

Upvotes: 0

Views: 57

Answers (2)

Aleksej
Aleksej

Reputation: 22979

A way to get your result with a slightly different method could be something like:

select *
    from 
        (
        select name, surname, percent_csfd, row_number() over ( order by percent_csfd desc) as rank
        from actor
          inner join actor_movie
            using (actorId)
          inner join movie
            using (movieId)
          inner join rating
            using(ratingId)
        (
    where rank = 1

This uses row_number to evaluate the "rank" of the movie(s) and then filter for the movie(s) with the highest rating.

Upvotes: 0

SomeJavaGuy
SomeJavaGuy

Reputation: 7357

You just need to join the tables properly. Afterwards you can simply add the columns you´d like to select. The final select could be looking like this.

select ac.name, ac.surname, -- go on selecting from the different tables
from actor ac
inner join actor_movie amo
on amo.actorid = ac.actorid
inner join movie mo
on amo.movieid = mo.movieid
inner join rating ra
on ra.ratingid = mo.ratingid 
where ra.PERCENT_CSFD = 
  (select max(percent_csfd)
   from rating)

Upvotes: 2

Related Questions