1. 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?

Discussion in 'General PHP and MySQL Discussions' started by TheBigK, Oct 31, 2015.

  1. TheBigK

    TheBigK Well-Known Member

    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?
     
  2. Mouth

    Mouth Well-Known Member

    select * from xf_table where secondary_group_id like '%2%' or secondary_group_id like '%7%'
     
  3. Chris D

    Chris D XenForo Developer Staff Member

    Code:
    SELECT * FROM xf_user WHERE FIND_IN_SET(2, secondary_group_ids) OR FIND_IN_SET(7, secondary_group_ids)
     
  4. TheBigK

    TheBigK Well-Known Member

    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.
     
  5. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  6. TheBigK

    TheBigK Well-Known Member

    Thanks! Really appreciate your response. I got the query working. :)
     
  7. Xon

    Xon Well-Known Member

    @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.
     
  8. TheBigK

    TheBigK Well-Known Member

    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.
     
  9. Xon

    Xon Well-Known Member

    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.
     
    TheBigK likes this.
  10. Mouth

    Mouth Well-Known Member

    ... 4280 rows in set
    ...3882 rows in set

    Something wrong with that JOIN I think.
     
    Xon likes this.
  11. Xon

    Xon Well-Known Member

    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?
     

Share This Page