CODEWITHSUNDEEP
mysql
user5878053
user5878053

Reputation:

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

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 |   
+------------+---------------+

Upvotes: 1

Views: 250

Answers (3)

Jens
Jens

Reputation: 69440

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

Upvotes: 0

Ramalingam Perumal
Ramalingam Perumal

Reputation: 1427

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

Upvotes: 0

1000111
1000111

Reputation: 13519

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.

Upvotes: 2

Related Questions