how to compare varchar with array of values return using mysql

Question

I want to get id's of users who has partners with matching partners id. In users table I am supposed to save partners id as comma separated string. First I get partner id from partners table which is array of ids(multiple record). Then I have to compare this array of ids with partners_id in the users table. How to compare a string with array of values.

Here I tried by converting array of ids to string and then I compared. But it not helped. Someone suggest a way.

Query I tried

SELECT `user`.`userID` FROM `user` WHERE `user`.`groupID` = 1234 AND `user`.`partners_id LIKE (SELECT GROUP_CONCAT(partners.id) as pid FROM `partners` WHERE `partners`.`name` LIKE '%XX%' AND `partners`.`active` = 1)

Table Users

userID groupID partners_id
 1      4       1A,2B,3C
 2      3       1A
 3      4       2B
 4      4       5C,2B

Partners

id  name  active
1A  XXSS    1
1B  AAYY    0
1C  ZZSS    1
2B  CCXX    1
3C  XXBB    1
5C  CCDD    0

Possible duplicate of [where condition from a comma seperated varchar in mysql and codeigniter](https://stackoverflow.com/questions/18816116/where-condition-from-a-comma-seperated-varchar-in-mysql-and-codeigniter) — Cabrra, Oct 25 '18 at 14:43
"I am supposed to save partners id as comma separated string." Stop there please. Don't store as comma separated string. You should really follow the principles of normalization — Madhur Bhaiya, Oct 25 '18 at 14:45
@NigelRen and Cabrra My problem is different from the one you mentioned — Kavitha Velayutham, Oct 25 '18 at 14:52
i think you may find your solution here https://stackoverflow.com/questions/19101550/mysql-join-two-tables-with-comma-separated-values — Asif Thebepotra, Oct 25 '18 at 19:41

score 0 · Answer 1 · answered Oct 26 '18 at 07:41

Finally I find users userId for matching partners as follows. Thanks to @Asif Thebepotra, it helped to find the query.

SELECT users.userId FROM users INNER JOIN partners on FIND_IN_SET(partners.id,users.partners_id) > 0 where partners.name like '%XX%' AND partners.ative = 1;

how to compare varchar with array of values return using mysql

1 Answers1