CODEWITHSUNDEEP
sql
Madura Harshana
Madura Harshana

Reputation: 1299

Error single-row subquery returns more than one row

This query returns collection of data. but I only need customer list.

select * from customer where cust_acnt_nbr = (select cust_acnt_nbr from Asset where dstrct_id = (select dstrct_id from dstrct where dstrct_nm = 'ATLANTA'))

please help me to get customer list

Upvotes: 2

Views: 90

Answers (2)

Steph Locke
Steph Locke

Reputation: 6146

I try to avoid subqueries as they can have poor performance and make it unclear what you're trying to achieve. You can usually convert it to a series of simple joins which are easier to read.

select c.* 
from customer c
inner join Asset a on c.cust_acnt_nbr = a.cust_acnt_nbr 
inner join dstrct d on d.dstrct_id =a.dstrct_id 
where dstrct_nm = 'ATLANTA'

Upvotes: 1

Andomar
Andomar

Reputation: 238076

Use in instead of = if a subquery can return multiple rows:

select * from customer where cust_acnt_nbr IN
    (select cust_acnt_nbr from Asset where dstrct_id IN
        (select dstrct_id from dstrct where dstrct_nm = 'ATLANTA'))

Upvotes: 3

Related Questions