CODEWITHSUNDEEP
sqldatabaseoracle-database
DazedNConfused
DazedNConfused

Reputation: 199

ORACLE - return record by comparing foreign key to each of the returned key

I have a SQL statement which returns the "single-row subquery returns more than one row" error in Oracle:

SELECT * FROM lds_placement 
WHERE fk1_account_id = 
  (SELECT lds_account.account_id 
   FROM lds_account 
   WHERE lds_account.fk1_consultant_id = 
     (SELECT consultant_id 
      FROM lds_consultant 
      WHERE UPPER(cst_name) LIKE UPPER(:app_user) || ' %' ));

How can I compare and display all records from lds_placement where the fk1_account_id is equal to either one of the returned account_id from lds_account table?

Upvotes: 0

Views: 14

Answers (1)

Stu
Stu

Reputation: 15769

If a sub-query can return a set (of values) as opposed to a single (scalar) value, you cannot use =. Have you tried using IN instead?

(To elaborate: what would something = (1,2,3) mean... one or all of them? It seems you want something is one of (1,2,3), which in SQL is something IN (1,2,3) -- to wit, replace = with IN in your query and you should be good to go.)

Upvotes: 1

Related Questions