1

How to SELECT values if id available in column(Comma separated values) using MySQL?

Here I need to get all values when the given id=17 available in group_id column.

Table:

+------------+---------------+
| user_id    | group_id      |
+------------+---------------+
|          1 |         1,2,3 |
|          3 |      12,23,17 |
|          5 |         17,26 |   
+------------+---------------+

I try:

SELECT * FROM `group` WHERE units_id IN('17'); //No result

Expecting result:

+------------+---------------+
| user_id    | group_id      |
+------------+---------------+
|          3 |      12,23,17 |
|          5 |         17,26 |   
+------------+---------------+

3 Answers3

2

You can use FIND_IN_SET

SELECT * FROM `group` WHERE FIND_IN_SET(17,group_id);

Note: It's highly discouraged to store comma separated values in column. A must read:

Is storing a delimited list in a database column really that bad?

Yes

Also you shouldn't use MySQL reserved words as your identifer's name. Be careful to enclose by backtick while using.

Community
  • 1
  • 1
1000111
  • 13,169
  • 2
  • 28
  • 37
0

You can use ´find_in_set`

SELECT * FROM `group` WHERE find_in_set('17',units_id );

IN checks if a column contains values from a comma separated list

It is very bad db design if you store values as csv.

For more infoemartion see mysql documentation

Jens
  • 67,715
  • 15
  • 98
  • 113
0

Try this one. You can use find_in_set :

SELECT * FROM `user` WHERE find_in_set('17',group_id) ORDER BY user_id;

RESULT:

+------------+---------------+
| user_id    | group_id      |
+------------+---------------+
|          3 |      12,23,17 |
|          5 |         17,26 |   
+------------+---------------+

REF: MySQL query finding values in a comma separated string

Community
  • 1
  • 1
Ramalingam Perumal
  • 1,367
  • 2
  • 17
  • 46