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

[TheBigK]

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?

 

[Mouth]

select * from xf_table where secondary_group_id like '%2%' or secondary_group_id like '%7%'

 

[Chris D]

FIND_IN_SET -> won't allow me to enter two values to search

SELECT * FROM xf_user WHERE FIND_IN_SET(2, secondary_group_ids) OR FIND_IN_SET(7, secondary_group_ids)

 

[TheBigK]

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]

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.

 

[TheBigK]

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.

Thanks! Really appreciate your response. I got the query working.

 

[Xon]

@TheBigK There is a better way without requiring visiting every user record to parse the comma separated list:

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]

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]

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]

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

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]

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?