How can handle this ERROR 1242 (21000): Subquery returns more than 1 row

Question

Hello everyone I am having issue with SQL query, have googled a lot but couldnt find any solution. here is my code: ``` SELECT (SELECT ltp FROM eod_stock WHERE company_id IN (1,2) AND entry_date = '2013-09-24') AS b, (SELECT ltp FROM eod_stock WHERE company_id IN (1,2) AND entry_date = '2013-09-25') AS e, CAST(((SELECT ltp FROM eod_stock WHERE company_id IN(1,2) AND entry_date = '2013-09-25') - (SELECT ltp FROM eod_stock WHERE company_id IN (1,2) AND entry_date = '2013-09-24')) / (SELECT ltp FROM eod_stock WHERE company_id IN (1,2) AND entry_date = '2013-09-25')*100 AS DECIMAL(10,2)) AS c; ``` it gives me error ``` ERROR 1242 (21000): Subquery returns more than 1 row ``` I understand the error cause my query should return more than 1 row as i am passing 2 company but i need all companies ltp, that i mentioned in my query. The result is need is like: ``` +--------+--------+--------+ | b | e | c | +--------+--------+--------+ | 460.00 | 338.00 | -36.09 | +--------+--------+--------+ | 3…

Accepted Answer

When you select from a subquery you can only get a single row.

You can join the table on itself and select the rows with the two dates you are interested in. Something like this should work.

```
SELECT a.ltp, b.ltp, CAST(((b.ltp - a.ltp) / b.ltp * 100) AS DECIMAL(10, 2))
FROM eod_stock a
JOIN eod_stock b ON a.company_id = b.company_id
WHERE a.entry_date = "2013-09-24"
AND b.entry_date = "2013-09-25"
AND a.company_id IN (1, 2)
```

How can handle this ERROR 1242 (21000): Subquery returns more than 1 row

Solution

Problem