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
What Ocr Options Exist Beyond Tesseract
Are PHP Associative Arrays Ordered
Get Coupon Data from Woocommerce Orders
Errorbag Is Always Empty in Laravel 5.2
MySQL and PHP - Insert Null Rather Than Empty String
How to Alias the Name of a Column in Eloquent
How to Send a Data to a Web Server from Android
How to Use In_Array If the Needle Is an Array
Backslash in PHP -- What Does It Mean
Ruby and PHP Hmacs Not Agreeing
Replace Only First Match Using Preg_Replace
Trying to Access Array Offset on Value of Type Null
PHP Password_Verify() Hash and Pass Won't Match
How to Include External CSS and Js File in Laravel 5
Using Wget to Run a Cronjob PHP
What Are the Valid Characters in PHP Variable, Method, Class, etc Names