CODEWITHSUNDEEP
databaseoracle-database
Banjar Machine
Banjar Machine

Reputation: 3

Single Subquery returns multiple rows in Oracle

select distinct a.person.name, b.title,b.director.name 
  from movie_roles a, movie b
 where a.person.name=
 ( select b.director.name 
     from movie b, movie_roles a
    where b.director.name=a.person.name)
  and b.movieID=a.movie.movieID;

I keep getting error that saying single single subquery returns multiple rows in Oracle.
Can anyone help me to solve this problem?

Upvotes: 0

Views: 64

Answers (1)

geoandri
geoandri

Reputation: 2428

It's self explanatory.In the following line 

where a.person.name= ( select b.director.name from movie b, movie_roles a where b.director.name=a.person.name)

you get more than one result so you cannot use "=". Try 

where a.person.name IN( select b.director.name from movie b, movie_roles a where b.director.name=a.person.name)

Upvotes: 2

Related Questions