ratVien ratVien - 1 year ago 259
MySQL Question

Mysql SELECT from list of values

I have 2 tables, "orders" and "genlist". In genlist table some generated info, id|date|order_ids(1,2,3,4...). I need to select all orders from "orders" by orders_ids value list from some date.

id | date| order_ids
1 | 00-00| 1,2,3,4
2 | 00-00| 5,6,7,8

SELECT * FROM orders
select order_ids from genlist
where date ='2016-07-04'

It,s not working properly, return only first element of list order_ids(1,2,3,4...). Just "1"
How can I write this?

Answer Source

I resolve this problem.

select * from orders 
where FIND_IN_SET(id, (select group_concat(order_ids) 
from gen_list))

It's works well , but It should be mentioned that

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download