CODEWITHSUNDEEP
mysqlsql
Roy
Roy

Reputation: 3267

How to workaround selecting multiple columns inside a single case expression

I am wondering if there is any way to select two columns based on the outcome of an if statement.

Here is what I have so far ( I have cut off half of the unnecessary stuff ):

  ( SELECT IF(
               (SELECT g.id
                FROM users AS u
                INNER JOIN user_groups AS g ON (g.id = u.group_id)
                WHERE u.id = 2) >= 4,

               (SELECT p.action, p.id
                FROM permissions AS p
                WHERE p.required_points <=
                    (SELECT reputation
                     FROM users
                     WHERE id = 2)),

               (SELECT 0, 0)))

Basically, is there any multiple column operand that behave like if statements? and if so how would I implement them. Any links to resources will be greatly appreciate.

The error is:

#1241 - Operand should contain 1 column(s)

The entire sql query:

(SELECT p1.action,
          p1.id
   FROM user_permissions AS u1
   INNER JOIN permissions AS p1 ON (p1.id = u1.action_id)
   WHERE u1.user_id = 2)
UNION
  (SELECT p2.action,
          p2.id
   FROM users AS u2
   INNER JOIN user_groups AS g ON (g.id = u2.group_id)
   INNER JOIN group_permissions AS gp ON (gp.group_id = g.id)
   INNER JOIN permissions AS p2 ON (p2.id = gp.action_id)
   WHERE u2.id = 2)
UNION
  ( SELECT IF(
               (SELECT g2.id
                FROM users AS u3
                INNER JOIN user_groups AS g2 ON (g2.id = u3.group_id)
                WHERE u3.id = 2) >= 4,
               (SELECT p3.action, p3.id
                FROM permissions AS p3
                WHERE p3.required_points <=
                    (SELECT reputation
                     FROM users
                     WHERE id = 2)),
               (SELECT 0, 0)))

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

Oh, so that is basically the whole query. This won't work:

SELECT IF( (SELECT g2.id
            FROM users u3 INNER JOIN
                 user_groups AS g2
                 ON g2.id = u3.group_id
            WHERE u3.id = 2) >= 4,
           (SELECT p3.action, p3.id
            FROM permissions AS p3
            WHERE p3.required_points <= (SELECT reputation FROM users WHERE id = 2)),
           (SELECT 0, 0)))

So, just move the queries to a from clause:

SELECT (case when g2.id >= 4, p3.action, 0),
       (case when g2.id >= 4, p3.id, 0)
FROM (SELECT g2.id
      FROM users u3 INNER JOIN
           user_groups AS g2
           ON g2.id = u3.group_id
      WHERE u3.id = 2
     ) g2 cross join
     (SELECT p3.action, p3.id
      FROM permissions AS p3
      WHERE p3.required_points <= (SELECT reputation FROM users WHERE id = 2))
    ) p3

If you don't really want the row with (0, 0), but it is there just so there is some other value, then let join or where filter it out:

SELECT p3.action, p3.id
FROM (SELECT p3.action, p3.id
      FROM permissions AS p3
      WHERE p3.required_points <= (SELECT reputation FROM users WHERE id = 2))
     ) p3
WHERE (SELECT g2.id
       FROM users u3 INNER JOIN
            user_groups AS g2
            ON g2.id = u3.group_id
       WHERE u3.id = 2
      ) >= 4

Upvotes: 1

Related Questions