2 Replies Latest reply on Jul 2, 2007 12:05 PM by cf_dev2

    SQL Search list for value

      Hello all,

      I am trying to search a huge table of users to see if they belong to a certain group. The group field would be as follows:

      group_id = 1,2,4,7

      In the interest of server memory, I do not want to pull the entire table and search with CF (listfind). Instead, I would like to run an SQL query like this:

      Select *
      FROM table
      where group_id CONTAINS '2'

      Of course I can't do this. I'm a liitle rusty on my SQL. Could someone help??

        • 1. Re: SQL Search list for value
          Level 7
          "where group_id LIKE '%2%'" is close to what you are looking for.
          Unfortunately this will also match the several twos in the following
          list "12,21,202". You will have to be very careful on distinguishing a
          value of 2 and a 2 as part of a larger value.

          This is a big reason why good data base design would normalize this kind
          of data into a join table so that on can easily distinguish between a 2
          and a 22.


          • 2. Re: SQL Search list for value
            cf_dev2 Level 1
            MySQL may have functions that allow you to find a value in a list. If not you may need to use a series LIKE statements. Either way the query will almost certainly be slower than if your data were properly normalized.

            Field = '2' OR Field LIKE '2,%' OR Field LIKE '%,2,%' OR Field LIKE '%,2'

            Storing data in that manner is generally considered a bad design. One of the reasons is what you've discovered: its difficult to query. I would strongly recommend that you normalize your data.

            User Table: UserID, UserName, ...
            Group Table: GroupID, GroupName, ...
            UserGroup Table:
            UserID | GroupID
            1 | 20
            1 | 2
            1 | 4
            1 | 7
            3 | 20
            3 | 89
            3 | 22
            5 | 122

            Your query would be simply

            SELECT UserID
            FROM UserGroup
            WHERE GroupID = 2