0

I have two tables in mysql as below:

Table1 holds the questions

qid  tid  qtype 
101    1    1
102    1    2
103    1    3
104    1    4

Here qtype is question type where qtype 1 and 2 accept numeric values and 3 and 4 accept string. I have declared qtype as varchar column.

Table2 holds the answers

sid qid  qtype qanswer
1    101    1     10
1    102    2     20
1    103    3     o1
1    104    4     o2

there are rows for every question for every 'sid'.

I have selected data in below fashion using following query:

select a.sid, MAX(CASE WHEN (a.qid = 101) THEN if(a.qtype in(1,2),cast(a.qanswer as unsigned),a.qanswer) ELSE NULL END) AS '101' ,
MAX(CASE WHEN (a.qid = 102) THEN if(a.qtype in(1,2),cast(a.qanswer as unsigned),a.qanswer) ELSE NULL END) AS '102' ,
MAX(CASE WHEN (a.qid = 103) THEN if(a.qtype in(1,2),cast(a.qanswer as unsigned),a.qanswer) ELSE NULL END) AS '103' ,
MAX(CASE WHEN (a.qid = 104) THEN if(a.qtype in(1,2),cast(a.qanswer as unsigned),a.qanswer) ELSE NULL END) AS '104' from Table2 a join Table1 b on a.qtype = b.qtpe where b.tid = 1

sid 101 102 103 104
1    10  20  o1  o2
2    30  15  o2  o1

I wanted to select qtype 1 and 2 as integer so I used below statement in my query:

if(qtype in (1,2),cast(qanswer as unsigned),qanswer)

I am not getting answer for qtype 1 and 2 as desired. I took reference from this question

I also tried convert instead of cast but got same result. How can I cast qtype 1 and 2 as integer?

How to return integer value from mysql expression.

eLRuLL
  • 18,488
  • 9
  • 73
  • 99
AMahajan
  • 189
  • 1
  • 3
  • 17

1 Answers1

0

This is not a statement:

if(qtype in (1, 2), cast(qanswer as unsigned), qanswer)

This is an expression. An expression returns a scalar value with a single type. MySQL needs to decide if the returned type is unsigned or varchar, because it must choose one for the entire expression.

MySQL would appear to be choose the string type.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • How do i make mysql to choose integer value rather than string value. – AMahajan Sep 16 '17 at 12:44
  • @AMahajan . . . That would be a different question from the one you asked. You should ask new questions as a *question* rather than a *comment*. Provide sample data and desired results. – Gordon Linoff Sep 16 '17 at 13:31