0

I have searched different threads but could not find the solution for mysql. So, posting a new question here.

I have a inner query that returns the below result:

20001,20002,2003

lets say, above result is value from a "accounts" column from "staff_details" table, which is a varchar.

Now I want above result in another query:

select * from users where account in (select accounts from staff_details where id =2)

But the above query always returns me 1 record(the first one in result).

Here my query is actually interpreted as(with quotes), since its varchar:

select * from users where account in ('20001,20002,2003')

How do I make it comma separated integer in mysql.

Bikas Katwal
  • 1,895
  • 1
  • 21
  • 42
  • Fix your data structure. You should not be storing a list of numbers as a comma-delimited string. You should have a separate table with one row per user and per account. – Gordon Linoff Nov 29 '17 at 12:27
  • yes, agree on that. Its been used from quite long and too many things are dependent, further have millions of such data. So, for now I just want to know how do I fix this – Bikas Katwal Nov 29 '17 at 12:30
  • You could split the list of numbers, insert them to a temp table and then join to it. But, I would seriously re-consider the tables structure. If you need to do the above, something is wrong with the relationships between the tables. – Tomer Shay Nov 29 '17 at 12:30

0 Answers0