Why does MySql give "Subquery returns more than 1 row" error?

Question

Hi guy i am selecting my active record from my table estates and all my other record are fine but the active record give me errors my code is

@query = Estate.find_by_sql"SELECT (e.name) as estate_name, g.name as governing_body,"+ "(select count(*) from stands s where s.estate_id = e.id AND #{filter_estates}) as total_stands, "+ "(select e.active from estates e where e.active = true AND #{filter_estates}) as estate_status, "+ "(select count(*) from services sp where sp.estate_id = e.id AND #{filter_estates}) as service_providers,"+ "(select count(*) from approved_vendors av where av.estate_id = e.id AND #{filter_estates})as vendors"+ " FROM estates e LEFT JOIN governing_bodies g on e.governing_body_id = g.id AND #{filter_estates} "

and i am getting an error.

(Mysql::Error: Subquery returns more than 1 row: SELECT (e.name) as estate_name, g.name as governing_body,(select count(*) from stands s where s.estate_id = e.id AND e.id IS NOT NULL) as total_stands, (select e.active from estates e where e.active = true AND e.id IS NOT NULL) as estate_status, (select count(*) from services sp where sp.estate_id = e.id AND e.id IS NOT NULL) as service_providers,(select count(*) from approved_vendors av where av.estate_id = e.id AND e.id IS NOT NULL)as vendors FROM estates e LEFT JOIN governing_bodies g on e.governing_body_id = g.id AND e.id IS NOT NULL ):

and i want to display a all estates which are active and inactive.

please guys,how can i solve this problem. i am using Mysql database.

Answer 1

It looks like you might have a problem with your third line:

(select e.active from estates e where e.active = true AND #{filter_estates}) as estate_status

The lines above and below that use an aggregate, so they only return one row, this could (probably is) returning multiple rows and it doesn't know which one to assign to estate_status.

You could probably just change that line to:

e.active as estate_status

Answer 2

I certainly am not familiar with the table so the best answer I can give you right now is why that query doesn't work.

select e.active from estates e where e.active = true AND #{filter_estates}) as estate_status

that line returns more than one row, which you can't do there. Notice your other ones use aggegrate functions so they do only return one row.

Oh, I don't use My SQL much, but in T-SQL we often do things like max(e.active) or perhaps put a top 1 (which I think is Limit 1 in my sQL)

Answer 3

Your subquery

(SELECT e.active FROM estates e WHERE   ...) AS estate_status

returns more than one value.

If you can, use 'TOP 1' like :

SELECT e.name AS estate_name   ,
       g.name AS governing_body,
       (SELECT COUNT(*) FROM stands s            WHERE   ...) AS total_stands,
       (SELECT TOP 1 e.active FROM estates e     WHERE   ...) AS estate_status,
       (SELECT COUNT(*) FROM services sp         WHERE   ...) AS service_providers,
       (SELECT COUNT(*) FROM approved_vendors av WHERE   ...) AS vendors
FROM   estates e
LEFT 
JOIN   governing_bodies g ON  e.governing_body_id = g.id
                          AND ...

Answer 4

A subquery in SELECT clause must return just 1 row and 1 column to be unambigous. This piece produces more than 1 row:

"(select e.active from estates e where e.active = true AND #{filter_estates}) as estate_status

change it to

"(select first(e.active) from estates e where e.active = true AND #{filter_estates}) as estate_status