CODEWITHSUNDEEP
mysqlsql
Martin Blore
Martin Blore

Reputation: 2195

MySQL use function column in where clause?

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.

Upvotes: 1

Views: 503

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Related Questions