Mysql WHERE IN Comma Separated

Question

I have a Mysql/MariaDb Table

ID  | name | partner
-----------------------
1     A      2
2     B      1
3     C      5,7
4     D      6,8
5     E      3
6     F      4
7     G      3
8     H      4

how to mysql query to search profile of partner with id = 3; And I want this result purely generated by the query or Stored Procedure/Function.

I have tried like this, but got the error:

SELECT id, name, partner from table WHERE id IN (SELECT id FROM table WHERE id=3);

I want output like this:

ID  | name | partner
-----------------------
5     E      3
7     G      3

partner column should be normalized before you can query partner id's like the way you do or use MySQL/MariaDB `FIND_IN_SET()` function — Raymond Nijland, May 16 '18 at 14:05
Yes. See normalisation. Don't bother considering any other option. — Strawberry, May 16 '18 at 14:11

Ron van der Heijden · Accepted Answer · 2018-05-16T14:17:29.827

1

In your sub-query you use:

SELECT id FROM table WHERE id=3

So your query becomes:

SELECT id, name, partner from table WHERE id IN (3);

But I guess you need:

SELECT partner FROM table WHERE id=3

Which results into:

SELECT id, name, partner from table WHERE id IN ('5,7');

Notice that this is a string and cannot be used in the IN function.

So you should, as mentioned by others, use find_in_set like so:

SELECT id, partner from docs WHERE FIND_IN_SET (3, partner) > 0;

edited May 16 '18 at 14:17

answered May 16 '18 at 14:06

Ron van der Heijden

14,803
7
58
82

Was just about to post about the same. Nice explanation of the current situation and how to fix it. One Thing you might add, would be to run the subquery for the IN statement on it's own to see what data that returns. – wawa May 16 '18 at 14:08
1

And how do you plan to handle the comma separated values? – Shadow May 16 '18 at 14:16
Thank you to all of you. After many attempts, apparently the comma separated value should not be spaced. `value: 3, 7 -> this error value: 3,7 -> this is successful` – Envy May 16 '18 at 15:28
The normalisation should be the accepted answer, not this. Even if you go down this route, the find_in_set() was suggested by sy else way ahead of this answer and is described in the duplicate questions. – Shadow May 16 '18 at 16:03

score 0 · Answer 2 · answered May 16 '18 at 14:08

0

As far as your partner field is comma separated string which contains IDs you have to use function FIND_IN_SET, and your query must looks like this:

SELECT id, name, partner from table WHERE FIND_IN_SET(id, partner) > 0

answered May 16 '18 at 14:08

cn007b

16,596
7
59
74

score 0 · Answer 3 · answered May 16 '18 at 14:13

A normalised schema might be as simple as the following...

ID  | name | partner
-----------------------
1     A      2
2     B      1
3     C      5
3     C      7
4     D      6
4     D      8
5     E      3
6     F      4
7     G      3
8     H      4

...but more probably, it will involve splitting the partners out to a separate table.

Mysql WHERE IN Comma Separated

3 Answers3