Saclar subquery produced more than one element

I have tables as

Cust1

+----+------+-------------+
| ID | Name | Referred_id |
+----+------+-------------+
|  1 | aaa  |             |
|  2 | bbb  |           1 |
|  3 | ccc  |           2 |
|  4 | ddd  |           2 |
|  5 | eee  |           4 |
+----+------+-------------+

Invoice

+-----+--------------+---------+
| ID  | billing_date | cust_id |
+-----+--------------+---------+
| 101 | 01-02-2017   |       1 |
| 102 | 01-03-2017   |       2 |
| bbb | 01-04-2017   |       3 |
+-----+--------------+---------+

I need the output as

+-----+--------------+-----------+---------------+
| ID  | billing_date | cust_name | referred_name |
+-----+--------------+-----------+---------------+
| 101 | 01-02-2017   | aaa       |               |
| 102 | 01-03-2017   | bbb       | aaa           |
| bbb | 01-04-2017   | ccc       | bbb           |
+-----+--------------+-----------+---------------+

Select c.id,
i.billing_date,
c.name,
(select
b.name as referred_name
from cust1 a left join cust1 b on a.referred_id=b.id)
from cust1 c, invoice i where c.id=i.cust_id

the query above is throwing error as scalar subquery produced more than one element. Can someone plz look into this.

Thanks

Answer

Your main problem is that your subquery is not correlated. In your main query you deal with the tables cust1 aliased c and invoice aliased i, but in your subquery you are not referencing c or i at all.

It seems you want:

select
  c.id, i.billing_date, c.name,
  (
    select cr.name
    from cust1 cr
    where cr.id = c.referred_id
  ) as referred_name
from invoice i 
join cust1 c on c.id = i.cust_id
order by c.id, i.billing_date;

You can achieve the same with an outer join:

select c.id, i.billing_date, c.name, cr.name as referred_name
from invoice i 
join cust1 c on c.id = i.cust_id
left join cust1 cr on cr.id = c.referred_id
order by c.id, i.billing_date;

Both queries are standard SQL, and very basic at that. I assume them to run in your environment, too.