SQL Search list for value

Question

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

Thanks

cf_dev2 · Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded