SQL Search list for value

[Guest]

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

[Newsgroup_User]

"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.

HTH

Ian

[cf_dev2]

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