CODEWITHSUNDEEP
mysqlsqlselectcase
ifallelsefailthenstackoverflow
ifallelsefailthenstackoverflow

Reputation: 135

select statement on CASE

Hi im trying to return a resultset from one table. However, I want to add another column on the result set, that is coming from a different table.

SELECT ID, NAME,
       CASE (SELECT STATUS FROM OTHERTABLE O WHERE O.ID = M.ID)
          WHEN '1' THEN 'In progress'
          WHEN '2' THEN 'Complete'
       END as STATUS
FROM MAINTABLE M

Im getting error: Subquery returned more than 1 value. This is not permitted...etc

Upvotes: 0

Views: 111

Answers (5)

J-Dizzle
J-Dizzle

Reputation: 3191

You should use limit 1, based on your error message.

SELECT M.ID, M.NAME,
    CASE WHEN O.STATUS = '1' THEN 'In progress'
         WHEN O.STATUS = '2' THEN 'Complete' 
    END as STATUS
FROM MAINTABLE M INNER JOIN OTHERTABLE O ON O.ID = M.ID LIMIT 1

Upvotes: 0

StuartLC
StuartLC

Reputation: 107267

In that case, you'll want a join, not a subquery:

SELECT ID, NAME,
CASE O.STATUS 
  WHEN '1' THEN 'In progress'
  WHEN '2' THEN 'Complete'
END as STATUS
FROM MAINTABLE M
INNER JOIN OTHERTABLE O ON O.ID = M.ID;

Upvotes: 1

Sadikhasan
Sadikhasan

Reputation: 18600

Might be you want to this

SELECT ID, NAME,
       CASE WHEN O.STATUS = '1' THEN 'In progress'
            WHEN O.STATUS = '2' THEN 'Complete'
       END AS STATUS
FROM MAINTABLE M
INNER JOIN OTHERTABLE O ON O.ID = M.ID

Upvotes: 1

Ashish Rajput
Ashish Rajput

Reputation: 1529

This is because your MainTable Id has more then one record in OtherTable

try it

SELECT ID, NAME,
   CASE 
      WHEN O.STATUS = '1'  THEN 'In progress'
      WHEN O.STATUS = '2' THEN 'Complete'
   END as STATUS
FROM MAINTABLE M JOIN OTHERTABLE O ON O.Id = M.ID

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Use join and organize your case

SELECT 
  M.ID,
  M.NAME,
  CASE
    WHEN O.STATUS = '1' 
    THEN 'In progress' 
    WHEN O.STATUS = '2' 
    THEN 'Complete' 
  END AS STATUS 
FROM
  MAINTABLE M 
  JOIN OTHERTABLE O 
    ON (O.ID = M.ID)

Upvotes: 2

Related Questions