MySQL use function column in where clause?

Question

I'm a bit rusty with my SQL skills at the moment and trying to understand how I resolve the issue in the following statements. I have a subquery that I have to select from because the column being generated via a function call can't be referenced in the WHERE clause of the same query. So I have to select over a sub-query so "BaseValue" can be used in the WHERE. My issue is that the sub-query is returning more than 1 column which is causing an error in MySQL when I run it.

The error message is:

"Error Code: 1241. Operand should contain 1 column(s)."

This is occurring around the second SELECT statement under the INSERT INTO statement.

Can anyone advise how I can overcome the problem most efficiently? The code in question:

CREATE TEMPORARY TABLE tSquads (
    Id INT(11) NOT NULL AUTO_INCREMENT,
    SquadId INT(11),
    SquadBaseValue INT(11),
    SquadName VARCHAR(50),
    PRIMARY KEY (ID)
) ENGINE=MEMORY;

INSERT INTO tSquads (SquadId, SquadBaseValue, SquadName)
    SELECT (temp.Id, temp.BaseValue, temp.Name) FROM (
        SELECT
            Id,
            f_GetSquadAverageBaseValue(Id) AS BaseValue,
            Name
        FROM
            squads
        ) temp
    WHERE
        Id <> pSquadId
        AND BaseValue BETWEEN vLowestAttackingBaseRate AND vHighestAttackingBaseRate
        AND BaseValue > 0
        AND ((vPercentageOfBaseRate / 100) * BaseValue) + BaseValue > vCurrentSquadBaseRate
    ORDER BY
         RAND();

Thanks.

Please edit your question and show the error. Your subquery looks fine. — Gordon Linoff
– Gordon Linoff, Commented Dec 16, 2015 at 21:43

Gordon Linoff · Accepted Answer · 2015-12-16 21:45:07Z

1

I am going to guess that this query might work:

INSERT INTO tSquads (SquadId, SquadBaseValue, SquadName)
    SELECT temp.Id, temp.BaseValue, temp.Name
    FROM (SELECT s.*, f_GetSquadAverageBaseValue(Id) AS BaseValue
          FROM squads s
         ) temp
    WHERE Id <> pSquadId AND
          BaseValue BETWEEN vLowestAttackingBaseRate AND vHighestAttackingBaseRate AND
          BaseValue > 0 AND
          ((vPercentageOfBaseRate / 100) * BaseValue) + BaseValue > vCurrentSquadBaseRate
    ORDER BY RAND();

Your WHERE clause is referencing many columns that are not provided by the subquery. By using s.*, you'll likely get all the columns you need.

answered Dec 16, 2015 at 21:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Blore Over a year ago

Thanks, that's working perfectly. Though confusing because in my original SQL, Im supplying 3 columns matching the 3 columns Im selecting in the outer query and the WHERE clauses is referencing all those columns too.

Collectives™ on Stack Overflow

MySQL use function column in where clause?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related