While working on a system I’m creating, I attempted to use the following query in my project:
SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by,
users.id AS posted_by_id
FROM users
WHERE users.id = posts.posted_by)
FROM topics
LEFT OUTER JOIN posts ON posts.topic_id = topics.id
WHERE topics.cat_id = :cat
GROUP BY topics.id
«:cat» is bound by my PHP code as I’m using PDO. 2 is a valid value for «:cat».
That query though gives me an error: «#1241 — Operand should contain 1 column(s)»
What stumps me is that I would think that this query would work no problem. Selecting columns, then selecting two more from another table, and continuing on from there. I just can’t figure out what the problem is.
Is there a simple fix to this, or another way to write my query?
Bill Karwin
524k85 gold badges650 silver badges812 bronze badges
asked Dec 26, 2012 at 22:08
Your subquery is selecting two columns, while you are using it to project one column (as part of the outer SELECT clause). You can only select one column from such a query in this context.
Consider joining to the users table instead; this will give you more flexibility when selecting what columns you want from users.
SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
users.username AS posted_by,
users.id AS posted_by_id
FROM topics
LEFT OUTER JOIN posts ON posts.topic_id = topics.id
LEFT OUTER JOIN users ON users.id = posts.posted_by
WHERE topics.cat_id = :cat
GROUP BY topics.id
answered Dec 26, 2012 at 22:10
cdhowiecdhowie
152k24 gold badges283 silver badges294 bronze badges
3
In my case, the problem was that I sorrounded my columns selection with parenthesis by mistake:
SELECT (p.column1, p.column2, p.column3) FROM table1 p WHERE p.column1 = 1;
And has to be:
SELECT p.column1, p.column2, p.column3 FROM table1 p WHERE p.column1 = 1;
Sounds silly, but it was causing this error and it took some time to figure it out.
answered Sep 7, 2020 at 15:15
Mauro BilottiMauro Bilotti
5,2904 gold badges41 silver badges63 bronze badges
3
This error can also occur if you accidentally use commas instead of AND in the ON clause of a JOIN:
JOIN joined_table ON (joined_table.column = table.column, joined_table.column2 = table.column2)
^
should be AND, not a comma
answered Apr 1, 2016 at 6:29
silkfiresilkfire
23.9k15 gold badges80 silver badges102 bronze badges
1
This error can also occur if you accidentally use = instead of IN in the WHERE clause:
FOR EXAMPLE:
WHERE product_id = (1,2,3);
Jin Kwon
19.3k12 gold badges107 silver badges169 bronze badges
answered May 26, 2017 at 9:43
2
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by,
users.id AS posted_by_id
FROM users
WHERE users.id = posts.posted_by)
Well, you can’t get multiple columns from one subquery like that. Luckily, the second column is already posts.posted_by! So:
SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
posts.posted_by
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by_username
FROM users
WHERE users.id = posts.posted_by)
...
answered Dec 26, 2012 at 22:10
Ry-♦Ry-
214k54 gold badges452 silver badges464 bronze badges
I got this error while executing a MySQL script in an Intellij console, because of adding brackets in the wrong place:
WRONG:
SELECT user.id
FROM user
WHERE id IN (:ids); # Do not put brackets around list argument
RIGHT:
SELECT user.id
FROM user
WHERE id IN :ids; # No brackets is correct
answered Oct 15, 2020 at 13:28
Janac MeenaJanac Meena
2,92530 silver badges30 bronze badges
2
This error can also occur if you accidentally miss if function name.
for example:
set v_filter_value = 100;
select
f_id,
f_sale_value
from
t_seller
where
f_id = 5
and (v_filter_value <> 0, f_sale_value = v_filter_value, true);
Got this problem when I missed putting if in the if function!
answered Aug 21, 2019 at 13:04
Another place this error can happen in is assigning a value that has a comma outside of a string. For example:
SET totalvalue = (IFNULL(i.subtotal,0) + IFNULL(i.tax,0),0)
kgui
3,9555 gold badges39 silver badges53 bronze badges
answered Sep 12, 2018 at 19:50
iAndyiAndy
113 bronze badges
(SELECT users.username AS posted_by,
users.id AS posted_by_id
FROM users
WHERE users.id = posts.posted_by)
Here you using sub-query but this sub-query must return only one column.
Separate it otherwise it will shows error.
answered Dec 8, 2019 at 5:53
I also have the same issue in making a company database.
this is the code
SELECT FNAME,DNO FROM EMP
WHERE SALARY IN (SELECT MAX(SALARY), DNO
FROM EMP GROUP BY DNO);
Nathan
75913 silver badges20 bronze badges
answered Oct 1, 2022 at 13:39
The error Operand should contain 1 column(s) is most likely caused by a subquery that’s returning more than one column.
Here’s a typical SELECT query that causes this error:
SELECT column_one,
(SELECT column_two, column_three FROM table_two)
FROM table_one;
The above subquery returns column_two and column_three, so MySQL throws the Operand should contain 1 column(s) error.
Most often, you only need to check your subquery and make sure that it returns only one column.
If you need more guidance on how to fix this MySQL error, then you may read the next section.
How to fix Operand should contain 1 column(s) error
To illustrate an example, imagine you have two tables that have related data named members and pets.
The members table contain the first_name of people who have pets as shown below:
+----+------------+----------------+
| id | first_name | country |
+----+------------+----------------+
| 1 | Jessie | United States |
| 2 | Ann | Canada |
| 3 | Joe | Japan |
| 4 | Mark | United Kingdom |
| 5 | Peter | Canada |
+----+------------+----------------+
While the pets table contain the owner and the species column as follows:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Ann | duck | 3 |
| 3 | Joe | horse | 4 |
| 4 | Mark | dog | 4 |
| 5 | Peter | dog | 5 |
+----+--------+---------+------+
The first_name and the owner columns are related, so you may use a subquery to display data from both tables like this:
SELECT `first_name` AS `owner_name`,
(SELECT `species`, `age`
FROM pets WHERE pets.owner = members.first_name)
FROM members;
However, the above SQL query is wrong, and it will throw an error like this:
ERROR 1241 (21000): Operand should contain 1 column(s)
This is because MySQL expects the subquery to return only one column, but the above subquery returns two.
To fix the error, you may create two subqueries with each subquery returning only one column as in the following SELECT statement:
SELECT `first_name` AS `owner_name`,
(SELECT `species`
FROM pets WHERE pets.owner = members.first_name) AS `species`,
(SELECT `age`
FROM pets WHERE pets.owner = members.first_name) AS `age`
FROM members;
While the above query works, it will throw another error once the subquery returns more than one row.
Let’s add another pet that’s owned by “Jessie” to the pets table as shown below:
+----+--------+---------+------+
| id | owner | species | age |
+----+--------+---------+------+
| 1 | Jessie | bird | 2 |
| 2 | Ann | duck | 3 |
| 3 | Joe | horse | 4 |
| 4 | Mark | dog | 4 |
| 5 | Peter | dog | 5 |
| 6 | Jessie | cat | 4 |
+----+--------+---------+------+
Now the subqueries will return two species and age rows for “Jessie”, causing another related error:
mysql> SELECT `first_name` AS `owner_name`,
-> (SELECT `species`
-> FROM pets WHERE pets.owner = members.first_name)
-> FROM members;
ERROR 1242 (21000): Subquery returns more than 1 row
To properly fix the error, you need to replace the subquery with a JOIN clause:
SELECT `first_name` AS `owner_name`, `species`, `age`
FROM members JOIN pets
ON members.first_name = pets.owner;
Subqueries can be used to replace JOIN clauses only when you need to SELECT data from one table, but you need to filter the result by another table column.
For example, maybe you have some owner names in the pets table that aren’t recorded in the members table. You can use a subquery in the WHERE clause to display rows in the pets table that are also recorded in the members table.
Here’s an example of using a subquery in the WHERE clause:
SELECT `owner`, `species`, `age`
FROM pets
WHERE `owner` IN (SELECT `first_name` FROM members);
Without using a subquery, you need to JOIN the table as shown below:
SELECT `owner`, `species`, `age`
FROM pets JOIN members
ON pets.owner = members.first_name;
The two queries above will produce the same result set.
And that’s how you can fix the Operand should contain 1 column(s) error in MySQL.
You need to check your subquery before anything else when you encounter this error.
Всем привет! Выходит это ошибка при вставке в таблицу данные.
Этот же код нормально работает для триггера Before Update, а для Before Insert выдает ошибку, типа много чем одной строки как я понял.
Вот мой код:
DROP TRIGGER IF EXISTS `InsertBuildingAnalysis`;
CREATE TRIGGER `InsertBuildingAnalysis` BEFORE INSERT ON `MDepartments` FOR EACH ROW BEGIN
DECLARE population INT;
DECLARE normative, isnornamtive INT;
DECLARE locality VARCHAR(1256);
DECLARE loc_dis_id int;
DECLARE loc_isCenter int;
DECLARE sub_pop int;
DECLARE sub_cpop int;
DECLARE dis_pop int;
SET population = (SELECT new_fnc(new.ParentId));
SET locality = (SELECT * FROM Localities WHERE Localities.Id = new.LocalityId);
SET loc_dis_id = (SELECT Localities.DistrictId FROM Localities WHERE Localities.Id = new.LocalityId);
SET loc_isCenter = (SELECT Localities.IsCenter from Localities WHERE Localities.Id = new.LocalityId);
SET sub_pop = (SELECT new_fnc2(new.SubRegionId));
SET sub_cpop = (SELECT new_fnc3(new.SubRegionId));
SET dis_pop = (SELECT new_fnc4(loc_dis_id));
IF (new.TypeId = 4 ) THEN
IF (population < 50) THEN
SET new.Normative = 0;
SET new.IsNormativePositive = 1;
ELSEIF (population > 8000) THEN
SET new.Normative = 0;
SET new.IsNormativePositive = 0;
ELSE
SET new.Normative = 1;
END IF;
ELSEIF (new.TypeId = 3) THEN
IF (population < 800) THEN
SET new.Normative = 1;
SET new.IsNormativePositive = 1;
ELSEIF (population > 2000) THEN
SET new.Normative = 0;
SET new.IsNormativePositive = 0;
ELSE
SET new.Normative = 1;
END IF ;
ELSEIF (new.TypeId = 2) THEN
IF (population > 800 && population <= 2000) THEN
SET new.Normative = 2;
ELSEIF (population > 2000 ) THEN
SET new.Normative = 1;
ELSE
SET new.Normative = 0;
SET new.IsNormativePositive = 1;
END IF;
ELSEIF (new.TypeId = 57) THEN
IF(population < 10000) THEN
SET new.Normative = 0;
SET new.IsNormativePositive = 1;
ELSE
SET new.Normative = 1;
END IF;
ELSEIF (new.TypeId = 26)THEN
IF (!locality) THEN
SET new.Normative = -1;
ELSEIF (loc_isCenter != 1) THEN
SET new.Normative = -2;
ELSE
SET new.Normative = 1;
END IF;
ELSEIF (new.TypeId = 35) THEN
SET population = sub_pop + sub_cpop;
IF(population >= 100000) THEN
SET new.Normative = 1;
ELSE
SET new.Normative = 0;
SET new.IsNormativePositive = 1;
END IF;
ELSEIF (new.TypeId = 27) THEN
SET population = dis_pop;
IF(population > 5000) THEN
SET new.Normative = 1;
ELSE
SET new.Normative = 0;
SET new.IsNormativePositive = 1;
END IF;
ELSEIF (new.TypeId = 25) THEN
SET new.Normative = 1;
END IF;
END;Не знаю как исправить! Ваши варианты пож-а!
You cannot have multiple columns being returned in a subquery like that, so you have several ways that you would have rewrite this query to work.
Either you can unpivot the data in the team table so you are only returning one column:
select *
from players
where sport='football'
and position='DEF'
and pname!='Binoy Dalal'
and pname not in (select player1
from team where sap='60003100009'
union all
select player2
from team where sap='60003100009'
union all
select player3
from team where sap='60003100009'
union all
select player4
from team where sap='60003100009'
union all
select player5
from team where sap='60003100009'
union all
select player6
from team where sap='60003100009'
union all
select player7
from team where sap='60003100009'
union all
select player8
from team where sap='60003100009')
order by price desc;
Or you can use a NOT EXISTS query:
select *
from players p
where sport='football'
and position='DEF'
and pname!='Binoy Dalal'
and not exists (select *
from team t
where sap='60003100009'
AND
(
p.pname = t.player1 OR
p.pname = t.player2 OR
p.pname = t.player3 OR
p.pname = t.player4 OR
p.pname = t.player5 OR
p.pname = t.player6 OR
p.pname = t.player7 OR
p.pname = t.player8
))
order by price desc;
Or you would have to use multiple WHERE filters on the player name:
select *
from players
where sport='football'
and position='DEF'
and pname!='Binoy Dalal'
and pname not in (select player1
from team where sap='60003100009')
and pname not in (select player2
from team where sap='60003100009')
and pname not in (select player3
from team where sap='60003100009')
and pname not in (select player4
from team where sap='60003100009')
and pname not in (select player5
from team where sap='60003100009')
and pname not in (select player6
from team where sap='60003100009')
and pname not in (select player7
from team where sap='60003100009')
and pname not in (select player8
from team where sap='60003100009')
order by price desc;
However, ideally you should consider normalizing the team table so you have one column with the player name and another column that assigns them a player number. Similar to this:
create table team
(
player varchar(50),
playerNumber int
);
Then when you are searching the team data you only have to join on one column instead of 8 different columns.
Я пытаюсь вставить данные из таблицы 1 в таблицу 2
insert into table2(Name,Subject,student_id,result)
select (Name,Subject,student_id,result)
from table1;
ключ для table2-student_id.
предположим, что дубликатов нет.
Я получаю ошибку: MySQL error 1241: Operand should contain 1 column(s)
в таблице 2 всего четыре столбца.
3 ответов
синтаксическая ошибка, удалить ( ) С select.
insert into table2 (name, subject, student_id, result)
select name, subject, student_id, result
from table1;
просто удалить ( и ) на вашем заявлении SELECT:
insert into table2 (Name, Subject, student_id, result)
select Name, Subject, student_id, result
from table1;
другой способ заставить парсер вызвать то же исключение-следующее неправильное предложение.
SELECT r.name
FROM roles r
WHERE id IN ( SELECT role_id ,
system_user_id
FROM role_members m
WHERE r.id = m.role_id
AND m.system_user_id = intIdSystemUser
)
вложенный элемент SELECT заявление в IN предложение возвращает два столбца, которые синтаксический анализатор видит как операнды, что технически правильно, так как столбец id соответствует значениям только из одного столбца (role_id) в результате, возвращаемом вложенным оператором select, который, как ожидается, вернет список.
для полноты, правильный синтаксис как следует.
SELECT r.name
FROM roles r
WHERE id IN ( SELECT role_id
FROM role_members m
WHERE r.id = m.role_id
AND m.system_user_id = intIdSystemUser
)
хранимая процедура, частью которой является данный запрос, не только проанализирована, но и возвращает ожидаемый результат.
Вопрос:
INSERT INTO People(Track_id_Reference)
SELECT track_id
FROM Tracks
WHERE track_title IN (SELECT tracktitle
FROM top100
WHERE artist IN (SELECT p.People_name, t.artist
FROM People AS p
RIGHT JOIN top100 AS t
ON
p.People_name=t.artist
UNION DISTINCT
SELECT p.People_name, t.artist
FROM People AS p
LEFT JOIN top100 AS t
ON
p.People_name=t.artist));Ошибка, которую я получаю,
ERROR 1241 (21000): Operand should contain 1 column(s)
подзапрос, объединения которого возвращают 2 столбца. Как я могу это исправить?
Лучший ответ:
вам не хватает FROM предложение
SELECT track_id
FROM tableName
WHERE track_title
поэтому полный запрос будет
INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM -- <<== add tableName here
WHERE track_title = (
SELECT tracktitle
FROM top100
WHERE artist = (
SELECT p.People_name,
t.artist
FROM People AS p
RIGHT JOIN top100 AS t
ON p.People_name = t.artist
UNION
DISTINCT
SELECT p.People_name,
t.artist
FROM People AS p
LEFT JOIN top100 AS t
ON p.People_name = t.artist
)
);
еще одна проблема, которая возникнет в ближайшее время, – это использование знака = в результате подзапроса, гораздо безопаснее использовать IN, чем =, потому что он будет генерировать исключение, если, например, в подзапросе будет возвращено более одного значения. Пример,
INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM -- <<== add tableName here
WHERE track_title IN (
SELECT tracktitle
FROM top100 .............