CODEWITHSUNDEEP
mysql
Hitendra
Hitendra

Reputation: 3236

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?

Upvotes: 0

Views: 2550

Answers (2)

Blank
Blank

Reputation: 12378

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

Upvotes: 1

Stranger
Stranger

Reputation: 134

You can use FIND_IN_SET(str,strlist)

Upvotes: 0

Related Questions