Hitendra Hitendra - 23 days ago 7
MySQL Question

How to compare comma separated values in mysql?

I have 3 tables.
users(id,name)
items(id,name,price) and
orders(id,user_id,item_id)

Here my item_id column has comma separated values like

[1,2,3]
. I have below query which works perfectly if item_id has single value.

select users.name,items.price
from orders
join users
on user.id = orders.user_id
join items
on items.id = orders.item_id
where orders.id = 1


Can any one suggest me how can i get the same result as in above query when there are comma separated values?

Answer Source

Try find_in_set function:

select users.name,items.price
from orders
join users
on user.id = orders.user_id
join items
on find_in_set(items.id, orders.item_id)
where orders.id = 1

However, store id with comma separated is a kind of bad db design, you'd better add a table to store the relationship between orders and items.

Just take an example:

create table order_item (
    order_id varchar(20),
    item_id varchar(20)
);

then the query:

select users.name,items.price
from orders
join users
on user.id = orders.user_id
join order_item oi on oi.order_id = orders.id
join items on oi.item_id = items.id
where orders.id = 1
group by orders.id

EDIT:

With [] included, you can try this:

select users.name,items.price
from orders
join users
on user.id = orders.user_id
join items
on find_in_set(items.id, replace(replace(orders.item_id, '[', ''), ']', ''))
where orders.id = 1