Mysql subquery in where clause that returns comma separated value

I’m not really good at subqueries, here’s the sample tables that I have.

table customers
=====================
id  | name | order_ids
1   | John | 1,2


table orders
=====================
id  | name
1   | apple
2   | orange

I’m trying to get the order names using this query, but I’m only getting one result. I’m not sure if this is possible.

select o.name 
from orders o 
where o.id IN(
    select c.order_ids 
    from customers c 
    where c.id=1
)

Answer

Your primary effort should go into fixing your design. You should not be storing several integer values in a string column. If each order belongs to a single customer, then the customer id should be stored in the orders table. If an order may belong to multiple customers at once, then you need a bridge table, with one row per customer/order tuple.

That said: for you current design, you can use find_in_set():

select o.*
from orders o
inner join customers c on find_in_set(o.id, c.order_ids)
where c.id = 1