Search code examples
sqloracleoracle-sqldeveloper

How to fix Ora-01427 single-row subquery returns more than one row error?

I have query like this and it gives me single-row subquery returns more than one row error:


SELECT COUNT(PERSONNEL_ID) 
FROM (SELECT DISTINCT * FROM CUSTOMERS)
WHERE CUSTOMER_ID = (SELECT CUSTOMER_ID FROM TRANSACTIONS)

Which tries to get count of distinct PERSONNEL_ID from customers table where CUSTOMER_ID at customers table and CUSTOMER_ID at transactions table are equal

Can you tell me how to fix my query?

Solution

  • You have more than one customer in transactions. Presumably, you intend:

    WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM TRANSACTIONS)

    Which you can also phrase as:

    WHERE CUSTOMER_ID = ANY (SELECT CUSTOMER_ID FROM TRANSACTIONS)