Subquery Returning More Than 1 Row

Solution to subquery returns more than 1 row error

= can be used when the subquery returns only 1 value.

When subquery returns more than 1 value, you will have to use IN:

select * 
from table
where id IN (multiple row query);

For example:

SELECT *
FROM Students
WHERE Marks = (SELECT MAX(Marks) FROM Students) --Subquery returns only 1 value

SELECT *
FROM Students
WHERE Marks IN
(SELECT Marks
FROM Students
ORDER BY Marks DESC
LIMIT 10) --Subquery returns 10 values

Subquery returns more than 1 row when aggregation is used

Edit: Based on table structure and output required, I updated the query and removed unwanted where and left join.

I don't know what you're trying to do. But based on my understanding I corrected your query:

SELECT
employee.employee_id AS employee_id,
profit_left.employee_department
profit_left.company_profit
FROM
employee
JOIN (
SELECT
company.employee_id,
company.employee_department,
SUM(company.company_profit_left) as company_profit
FROM company
GROUP BY company.employee_id,company.employee_department
) AS profit_left on employee.employee_id=profit_left.employee_id

Only in my server returns Subquery returns more than 1 row

It means your server has different data stored in database: there are multiple rows with same meta_key (if error is related with queries with meta_key, if not - adjust column name)

Add GROUP BY meta_key or LIMIT 1 to your sub-queries. E.g.

SELECT SQL_CALC_FOUND_ROWS p.*, 
(SELECT meta_value FROM wp_postmeta pm WHERE pm.post_id = p.ID AND meta_key = 'localidad' LIMIT 1) AS 'localidad',
...

Subquery returns more than 1 row in select statement

Use a join:

SELECT t1.orderDate,
t1.status,
t2.prices
FROM orders t1
INNER JOIN
(
SELECT orderNumber,
SUM(quantityOrdered * priceEach) AS prices
FROM orderdetails
GROUP BY orderNumber
) t2
ON t1.orderNumber = t2.orderNumber

How to overcome MySQL 'Subquery returns more than 1 row' error and select all the relevant records

GROUP_CONCAT with SEPARATOR and simplify your query a bit:

SELECT users.name AS name,
(SELECT GROUP_CONCAT(email_address.email_address SEPARATOR ', ')
FROM email_address
WHERE email_address.user_id = users.id) AS email
FROM users

Reference: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

Subquery returns more than 1 row when use select to set value of parameter

The sub-query can return multiple rows.

Even if it shouldn't, that won't do.

But you can change it to this.

SELECT @V_SOURCE = ITEM_SOURCE
FROM TABLE1
WHERE OPP_CODE = @V_OPP_CODE
AND PDGROUPNO = @V_PRD_GROUP_NO
AND DELETE_FLAG IS NULL
AND CONTRACTOR = @V_CONTRACTOR
AND OPP_ITEM_NO = @_OPP_ITEM_NO
GROUP BY ITEM_SOURCE;

It'll assign the last value of the resultset to the variable.

Which is fine, since you expect only one anyway.

Another way is to pick only the top 1

SET @V_SOURCE = (
SELECT TOP 1 ITEM_SOURCE
FROM TABLE1
WHERE OPP_CODE = @V_OPP_CODE
AND PDGROUPNO = @V_PRD_GROUP_NO
AND DELETE_FLAG IS NULL
AND CONTRACTOR = @V_CONTRACTOR
AND OPP_ITEM_NO = @_OPP_ITEM_NO
);

Error Code: 1242. Subquery returns more than 1 row in Attribute Subquery

The reason why you're getting that error is because a subquery can't return more than 1 result.

Try this:

SELECT     
a.subject_id,
a.hadm_id,
a.admittime,
a.deathtime,
a.diagnosis,
p.drug,
CASE WHEN a.deathtime <> '0000-00-00'THEN DATEDIFF(deathtime, admittime) AS Diff
FROM
admissions AS a INNER JOIN prescriptions AS p
ON a.subject_id = p.subject_id
WHERE diagnosis = 'CARDIAC ARREST';


Related Topics



Leave a reply



Submit