• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

SQL Help: How do I search for two numbers in comma separated list?

TheBigK

Well-known member
#1
I'm building this query that requires me to check if a specific values exist inside 'secondary_group_id'. For example, say my secondary_group_id contains values like -

Row# => secondary_group_id
1 => 5,6,9
2 => 4,5,6
3 => 2,4,7
4 => 2,4,6
5 => 7,8,9
6 =>2,7,9

and I want to select only the rows that have ids: 2 or 7. That is ros #3, 4, 5 and 6. How do I go about it?

FIND_IN_SET -> won't allow me to enter two values to search
LIKE?
OR LOCATE?
 

TheBigK

Well-known member
#4
Thanks @Mouth and @Chris D . Really appreciate your responses. I'm wondering if I had to match more values, is there any other way I can write above query? I guess using 'OR' would have its own limitations.
 

Chris D

XenForo developer
Staff member
#5
Perfectly reasonable to keep using OR. Obviously you can use AND too depending on your exact requirements. Or you can combine them and wrap them in parenthesis.
 

Xon

Well-known member
#7
@TheBigK There is a better way without requiring visiting every user record to parse the comma separated list:

Code:
SELECT user.*
FROM xf_user_group_relation AS relation
JOIN xf_user AS user ON user.user_id = relation.user_id
WHERE relation.user_group_id in (6,7) and  relation.is_primary = 0
You can remove the relation.is_primary clause if you want to search for any group membership.
 

TheBigK

Well-known member
#8
Thanks Xon. IN (x,y) is something new to me. Will explore it. Curious to know how to decide what approach is the best? I'm totally new to writing SQL queries and hence stuck at the basics.
 

Xon

Well-known member
#9
Thanks Xon. IN (x,y) is something new to me. Will explore it. Curious to know how to decide what approach is the best? I'm totally new to writing SQL queries and hence stuck at the basics.
IN (x,y) is just a more compact way of writing "(relation.user_group_id = 6 or relation.user_group_id = 7)"

I prefer to use this method (join + where clause) as it allows the database to use indexes to only selected required data. Otherwise, every user row must be visited to load, parse, and test the 'secondary_group_id' column.
 

Mouth

Well-known member
#10
select * from xf_table where secondary_group_ids like '%17%' or secondary_group_ids like '%27%';
SELECT * FROM xf_user WHERE FIND_IN_SET(17, secondary_group_ids) OR FIND_IN_SET(27, secondary_group_ids);
... 4280 rows in set
Code:
SELECT user.*
FROM xf_user_group_relation AS relation
JOIN xf_user AS user ON user.user_id = relation.user_id
WHERE relation.user_group_id in (17,27) and  relation.is_primary = 0
...3882 rows in set

Something wrong with that JOIN I think.
 

Xon

Well-known member
#11
Ugh, this explains some issues with my User Mention Improvements add-on.

XenForo populates xf_user_group from the secondary_group_ids, so it should have been a valid query.

@Mike or am I misunderstanding how this table is used?