CODEWITHSUNDEEP
mysql
Beck
Beck

Reputation: 1

mysql error keeps appearing - #1241- Operand should contain 1 column(s) - what does it mean?

When I run my query in mysql I keep getting the following error:

Operand should contain 1 column(s)

How do I fix it?

SELECT( SELECT Tag, COUNT(*) AS magnitude 
FROM  (SELECT a.* 
FROM sport b 
join sport a 
on a.Tag = b.Name
where b.sport_ID > a.sport_ID and a.Tag = 'Football') as ball
LIMIT 1 )

Upvotes: 0

Views: 264

Answers (2)

Srihari Karanth
Srihari Karanth

Reputation: 2167

The final select doesn't have any column specified. Try this:

SELECT * from ( SELECT Tag, COUNT(*) AS magnitude 
FROM  ( SELECT * 
FROM sport b 
join sport a 
on a.Tag = b.Name
where b.sport_ID > a.sport_ID and a.Tag = 'Football') as ball
group by Tag
LIMIT 1 ) as ball2

Even b.sport_ID > b.sport_ID doesnt make sense.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562310

Your subquery has two columns: Tag and magnitude. This is not a scalar subquery.

The explanation of the error is that every subquery in the select-list must be a scalar subquery, which means it must return exactly one column and one row.

Example: Let's keep it simple and use queries of literal values, so we aren't even referencing tables. We assign a column alias for each item in the select-list.

SELECT 
  123 AS colum1,
  456 AS column2,
  (SELECT 789, 'abc') AS column3;

But wait! How can both 789 and 'abc' be assigned the column alias column3? That's ambiguous.

It turns out it's not allowed, and will generate an error message, because it's ambiguous. The subquery returned two columns, but this violates the rule that each item in the select-list of the outer query must be one column. We can fix it this way:

SELECT 
  123 AS colum1,
  456 AS column2,
  (SELECT 789) AS column3,
  (SELECT 'abc') AS column4;

Now each item in the select-list returns just one column.

A similar rule exists that requires a subquery in the select-list to return no more than one row, too:

SELECT 
  123 AS colum1,
  456 AS column2,
  (SELECT some_column FROM table_with_multiple_rows) AS column3;

This is wrong because the subquery returns multiple values, and there's no way to stuff all those values into one row of the result in the outer query.

There are a couple of ways to solve this. Make sure the subquery returns no more than one row. Either use a WHERE clause to specify one row (a condition on the primary or unique key of the table):

SELECT 
  123 AS colum1,
  456 AS column2,
  (SELECT some_column FROM table_with_multiple_rows
   WHERE unique_column = 789) AS column3;

Or else use LIMIT 1:

SELECT 
  123 AS colum1,
  456 AS column2,
  (SELECT some_column FROM table_with_multiple_rows
   LIMIT 1) AS column3;

Either way, your subquery will return at most one row (it could match zero rows, in which case the result of the subquery will be NULL).

Upvotes: 1

Related Questions