"Truncated incorrect DOUBLE value" in MySQL

Question

Trying to run below query but its showing the error:

Truncated incorrect DOUBLE value

Can anybody explain me, what I'm missing?

SELECT exam_type_name
FROM exam_types 
WHERE exam_type_id IN (SELECT ids FROM exam WHERE id = 37);

Above subquery returns string 36,37,39,40

Primary Table Defination:

CREATE TABLE  `careerturn_db`.`exam_types` (
  `exam_type_id` int(11) NOT NULL AUTO_INCREMENT,
  `exam_type_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`exam_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;

Subquery Table Definition:

CREATE TABLE  `careerturn_db`.`exam` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ids` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;

Error:

Yes, it's not a correct `DOUBLE` value. What are you trying to achieve? — Raptor, Jun 01 '17 at 06:12
im trying to get all `exam_type_name` who has `36,37,39,40` ids — Santosh Jadi, Jun 01 '17 at 06:14
What is the type of that `id` column? Can you post the table definitions? — Tim Biegeleisen, Jun 01 '17 at 06:14
And your subquery doesn't make sense: `SELECT id FROM exam WHERE id = 37`. `id` is always 37. — Raptor, Jun 01 '17 at 06:14

Sebastian Brosch · Accepted Answer · 2017-06-01T06:34:33.290

2

You can try the following using FIND_IN_SET:

SELECT exam_type_name
FROM exam_types e
WHERE FIND_IN_SET(exam_type_id, (SELECT ids FROM exam WHERE id = 37)) > 0;

The error of your original query is thrown because you try to search a integer value in a string which looks like a DOUBLE value. But MySQL can't convert this value to DOUBLE with multiple ,.

edited Jun 01 '17 at 06:34

answered Jun 01 '17 at 06:28

Sebastian Brosch

42,106
15
72
87

"Truncated incorrect DOUBLE value" in MySQL

1 Answers1