CODEWITHSUNDEEP
mysql
user2651906
user2651906

Reputation: 193

How to convert comma separated numbers to integer mysql query GROUP_CONCAT

I have a comma separated numbers like '46,95,44' which is the output obtained from GROUP_CONCAT 

select GROUP_CONCAT (prodid) from tablename where condition;

I want to convert this to integer such that i can use this as the input to the "in" operation

select * from table2 where prodid in 
(
   select GROUP_CONCAT (prodid) from tablename where condition
);

The above query results only base on the first number in the comma separated list . How to convert the list in to integer

output :  46,95,44

Upvotes: 1

Views: 3472

Answers (1)

juergen d
juergen d

Reputation: 204756

Remove the group_concat

select * from table2 
where prodid in 
(
  select prodid
  from tablename
  where condition
);

group_concat produces a single string with ids, but you need seperate values for the IN clause.

With group_concat your query compiles to

select * from table2 
where prodid in ('46,95,44');

but you need

select * from table2 
where prodid in ('46','95','44');

Upvotes: 3

Related Questions