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?

Answer

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)

Advertisement

Answer