CODEWITHSUNDEEP
mysqlsql
Zabs
Zabs

Reputation: 14142

Operand should contain 1 column(s) - in mysql query

I have the following complex query that is giving me an error Operand should contain 1 column(s)

Can anyone suggest what is wrong

SELECT
t.user_id AS user_id,
t.organisation_id AS organisation_id,
t.firstname AS firstname,
t.surname AS surname,
t.username AS username,
t.year_id AS year_id,
t.form_name AS form_name,
t.House AS House,
rcPoints.total AS milestoneRedeemedCodesTotal,
rcFilteredPoints.total AS redeemedCodesTotalFiltered,
(
    COALESCE (rcFilteredPoints.total, 0) - COALESCE (milestoneHistory.total, 0)
) AS redeemedCodesTotalAvailableFiltered,
ABS(
    FLOOR(
        (
            COALESCE (rcFilteredPoints.total, 0) - COALESCE (milestoneHistory.total, 0)
        ) / 1000
    ) * 1000
) AS redeemedCodesTotalTowardsMilestone,
ABS(
    FLOOR(
        (
            COALESCE (rcFilteredPoints.total, 0) - COALESCE (milestoneHistory.total, 0)
        ) / 1000
    )
) AS redeemedCodesMilestoneTriggers,
COALESCE (milestoneHistory.total, 0) AS historyTotal
FROM
`myuser` `t`
LEFT JOIN (
SELECT
    rc.user_id AS user_id,
    SUM(rc.school_points) AS total
FROM
    `redeemed_codes` `rc`
INNER JOIN myuser m ON (m.user_id = rc.user_id)
WHERE
    (rc.date_redeemed >= 0)
AND (m.organisation_id = 58022)
GROUP BY
    rc.user_id
) AS rcPoints ON (rcPoints.user_id = t.user_id)
LEFT JOIN (
SELECT
    rc.user_id AS user_id,
    SUM(rc.school_points) AS total
FROM
    `redeemed_codes` `rc`
INNER JOIN myuser m ON (m.user_id = rc.user_id)
WHERE
    (rc.date_redeemed >= 0)
AND (m.organisation_id = 58022)
GROUP BY
    rc.user_id
) AS rcFilteredPoints ON (
 rcFilteredPoints.user_id = t.user_id
)
LEFT JOIN (
SELECT
    mh.user_id AS user_id,
    mh.milestone_id AS milestone_id,
    MAX(mh.points_when_triggered) AS total
FROM
    `milestone_history` `mh`
WHERE
    mh.milestone_id = 13
GROUP BY
    mh.user_id
) AS milestoneHistory ON (
milestoneHistory.user_id = t.user_id
)
WHERE
(
    (
        SELECT
            COALESCE (count(*), 0)
        FROM
            milestone_history mha
        WHERE
            mha.milestone_id = 13
        AND mha.user_id = t.user_id
    ) = 0
)
AND (t.organisation_id = 58022)

AND 
 ( 
 SELECT * FROM
redeemed_codes t1
WHERE
organisation_id = 1
AND
(
    SELECT
        sum(school_points)
    FROM
        redeemed_codes t2
    WHERE
        t2.redeemed_code_id <= t1.redeemed_code_id
) >= 1000
 ORDER BY redeemed_code_id
 LIMIT 1
)

GROUP BY
t.user_id
ORDER BY
redeemedCodesMilestoneTriggers DESC
LIMIT 1

Upvotes: 0

Views: 128

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query might have multiple errors, but this condition in the WHERE clause is definitely suspect and would lead to that error:

AND  (SELECT *
      FROM redeemed_codes t1
      WHERE organisation_id = 1 AND
            (SELECT sum(school_points)
             FROM redeemed_codes t2
             WHERE t2.redeemed_code_id <= t1.redeemed_code_id
            ) >= 1000
      ORDER BY redeemed_code_id
      LIMIT 1
     )

I have no idea what you are trying to do. Sometimes, the solution is simply EXISTS:

EXISTS (SELECT *
        FROM redeemed_codes t1
        WHERE organisation_id = 1 AND
              (SELECT sum(school_points)
               FROM redeemed_codes t2
               WHERE t2.redeemed_code_id <= t1.redeemed_code_id
              ) >= 1000
       )

Upvotes: 2

Related Questions