Why is MySql giving the error "Subquery returns more than 1 row"?
Hi guy i choose my active entry from my metropolitan estates and all my other entry is fine but active entry gives me errors, my code is
@query = Estate.find_by_sql "SELECT (e.name) as property_name, g.name as governing_body," + "(select count () from s tables 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 property_status, "+" (select count () from sp services 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 providers "+" FROM estates e LEFT JOIN g manag_bodies g on e .gring_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 property_name, g.name as control_body (select count () from s tables 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 property_status (select count () from sp services 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 government_bodies g on e.gating_body_id = g.id AND e.id NOT NO):
and I want to display all objects that are active and inactive.
Please guys how can I solve this problem. I am using Mysql database.
a source to share
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 property_status
The lines above and below that use an aggregate, so they only return one row, this may (possibly) return multiple rows and doesn't know which one to assign to property_status.
You could just change this line to:
e.active as property_status
a source to share
I am of course not familiar with the table, so the best answer I can give you right now is why this query is not working.
select e.active from estates e where e.active = true AND # {filter_estates}) as property_status
this line returns more than one line, which you cannot do. Note that others are using aggegrate functions, so they only return one string.
Oh, I don't use My SQL very much, but in T-SQL we often do things like max (e.active) or maybe put top 1 (which I think is the 1 limit in my sQL)
a source to share
Your subquery
(SELECT e.active FROM estates e WHERE ...) AS estate_status
returns more than one value.
If you can, use "TOP 1", for example:
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 ...
a source to share
A subquery in a SELECT clause must only return 1 row and 1 column to be unambiguous. This part creates more than 1 line:
"(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
a source to share