Operand should contain 1 column s ошибка

The “operand should contain 1 column(s)” could appear if your subquery selects two columns where you should select only one. There are a lot of other causes of this error as well. This article contains the experts’ solutions with coding examples.

Keep reading this article to learn everything about “operand should contain 1 column(s)” error.

Why You Are Getting the Operand Should Contain 1 Column(s) Error?

There could be many reasons to appear this error. Let’s see some of the possible causes.

– Subquery Selecting Two Columns

If you are using to select only one column and your subquery is selecting two columns, this error can appear. So make sure your subquery is not selecting two columns, because this error can occur in this scenario. You can select only one column from the query.

– Typing Mistakes

If you accidentally used the commas instead of “AND” in the “ON” clause of a “JOIN,” or used ‘=’ instead of “IN” in the “WHERE” clause, or missing “IF” function name, or using a parenthesis mistakenly, you can get this error.

– Comma Outside of a String

Another cause of this error could be the presence of a comma outside of a string. If you are assigning a value that has a comma outside of a string, you might get to see this error.

– Getting Error in Rails

If you are using an active record in SQL and passing a single value as an array, it is okay. However, if you are passing multiple values as an array, you might get the operand should contain 1 column(s) error. You can not pass it as an array to user_id. This could be the cause of the error in your case.

How To Fix the Error?

We covered all the causes of the error. Let’s now find the solutions to those causes.

– Subquery Returning Two Columns

If you are sure that the leading cause of this error is that your subquery is selecting two columns, the best solution for you could be to use the “users” table instead of the subquery. The “users” table will allow you to select what columns you want from users with flexibility.

– Another Way of Solving the Issue

Suppose you have two tables named “members” and “cars.” Where the “member” table contains the “first_name” and the “country” of the people who have cars. While the “cars” table includes the “owners,” “models,” and “conditions.” Keep in mind that we are adding five owners of five cars. Here the “first_name” and the “owner” columns are related, so you would want to use the subquery to display the data from both.

SELECT ‘first_name’ AS ‘owner’,
(SELECT ‘models’, ‘condition’
FROM cars WHERE cars.owner = members.first_name)
FROM members;

Here the bad news is the above query is wrong. Because it will throw the error code like operand should contain 1 column(s). Here the MYSQL wants the subquery to return only one column, but the subquery returns more than one column. So to fix this MYSQL error, we need to create two subqueries so that each subquery can return only one value and the error can be avoided.

Create Two Subqueries as the Following Method.

SELECT ‘first_name’ AS ‘owner’,
(SELECT ‘models’
FROM cars WHERE cars.owner = member.first_name) AS ‘models’,
(SELECT ‘age’
FROM cars WHERE cars.owner = members.first_name) AS ‘condition’
FROM members;

The above query is correct, but here is another bad news: now the subquery is returning more than one row because we added five owners of five cars. Hence, to avoid that error, let’s add one owner’s name with the car to the car’s table. The subquery will return the “model” and “condition” row for the newly added owner. And here, another error occurred.

So to fix all the errors, now we need to use the “JOIN” class instead of the subqueries. Keep in mind that you can only do this when you need to select data from one table, but you also need another table to filter the result. If you would have some owner’s name in the “cars” table that was not mentioned in the “members” table, then you should have used the subquery in the “WHERE” clause to display rows in the “cars” table that are also mentioned in the “members” table.

This is how you can use the subquery to avoid the operand should contain 1 column(s) error:

SELECT ‘owner’, ‘models,’ ‘age.’
FROM cars
WHERE ‘owner’ IN (SELECT ‘first_name’ FROM members);
But if you can’t use the subquery, this is how you can JOIN the table:
SELECT ‘owner’, ‘models,’ ‘condition.’
FROM cars JOIN members
ON cars.owner = members.first_name;

Both ways, you will get the same result and can avoid the operand should contain 1 column(s) error.

– Typing Mistakes

Make sure there is no typing mistake; if there is, correct it according to the syntax. Let’s see a few examples of the most common typing mistakes.

Examples of Some Typing Mistakes

JOIN joined_table ON (joined_table.column = table.column, joined_table.column2 = table.coumn2)

In the above example, the comma is placed between table.column and joined_table.column2 instead of “AND,” which is a mistake.

WHERE product_id = (4,5,6);

Here you can see that ‘=’ is mistakenly used instead of AND.

In the above example, you can see that brackets are used around the fields in the select clause, which is actually a mistake, and you are not following the correct syntax. In that case, you can get the operand should contain 1 column(s) error.

Also, ensure that you don’t use the parenthesis around column selection. This mistake is widespread in some beginner developers; they often write like this:

SELECT (p.column1, p.column2, p.column3) FROM table1 p WHERE p.column1 = 1;

The above line is the incorrect way of writing SELECT clauses where the correct way is as follows

SELECT p.column1, p.column2, p.column3 FROM table1 p WHERE p.column1 = 1;

– Comma Outside of a String

If the cause of this error is the presence of a comma in your case, then you should follow the correct syntax as follows.

SET totalvalue = (IFNULL(i.subtotal,0) + IFNULL(i.tax,0),0)

– Getting Error in Rails

If you need to pass the multiple values, make sure you pass a list of strings.

FAQs

– What Is the Mysql Group by Statement?

The operand should contain 1 column(s) GROUP BY statement in MYSQL groups the rows into summary rows of the same values. The GROUP BY statement is mostly used with the function like (COUNT(), MAX(), MIN(), SUM(), AVG()). The GROUP BY groups the result-set of these functions by one or more columns.

– What Is Mysql Join?

The MYSQL JOIN retrieves data from different tables when more than one table is joined in the SQL statement.

– How Many Types of Mysql Join?

There are multiple types of MYSQL JOINs:

• Mysql Inner Join

This type of JOIN is the most common type of join called the simple join. This JOIN returns all rows from multiple tables where more than one table is joined, and the join condition is met. This JOIN returns the result where all the tables intersect. The syntax of the MYSQL INNER JOIN is as follows:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

• Mysql Left Outer Join

The MYSQL LEFT OUTER JOIN, also known as LEFT JOIN, is used to return all the rows from the table on the left and only those rows from the other table where the join condition is met. As a result, you will get all the records of the table only those records of table two that intersect with table one. The syntax of the LEFT OUTER JOIN is as follows:

SELECT columns
FRO table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

• Mysql Right Outer Join

The MYSQL RIGHT OUTER JOIN, also known as RIGHT JOIN, returns all the rows of the right table and only those rows from the other table where the join condition is met. As a result, you will get all the records from the right table and only those from the left table that intersect the record of the right table. The syntax of this type of JOIN is as follows:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN ordersx`
ON suppliers.supplier_id = order.supplier_id;

Conclusion:

Let’s understand what we learned today:

• There could be many causes of the operand should contain 1 column(s) error, but the major reasons could be the subquery returning more than one column or any typing mistake.
• If the subquery is returning more than one column in your case, try using the USERS table to avoid this error.
• You can also use Subquery in the WHERE clause to display the rows of one table but mentioned in the other table.
• You can also use the JOIN class, but remember that you can only use this when you need to select data from one table and use the other table to filter that data.
• If you find any typing mistakes that lead you to face the error, correct that mistake by using the correct syntax.

We covered all the causes of the operand should contain 1 column(s) error. We understood why this error appeared, and we fingered out two primary reasons; the subquery returning more than two columns or typing mistake. If you be a little careful about these mistakes and avoid them, you won’t see this error. If you ever get the same error, you can use this article as a solution guide to that 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.