Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select value in field containing multiple values

Posted on 2006-11-12
6
Medium Priority
?
2,177 Views
Last Modified: 2012-05-05
I have a table (user) with a field named membergroupids which contains multiple values seperated by a comma.

The field type is varchar and will contain muiltiple member group id numbers.

for instance

Administrators group is 1
registered is 2
site users is 3
private group is 4

the field can contain this data in random entry.
one record could be   3,4
two could be             1,4
thre could be            4,3
4 could be                2,4


I am trying to figure out the sql statement that would select all records in the table that contain a 4 and ordered by userid.

examples I have tried :

SELECT * FROM user WHERE membergroupids IN (4) = 4 order by userid ASC
SELECT * FROM user WHERE membergroupids >= 4 order by userid ASC
SELECT * FROM user WHERE membergroupids IN ('4') = 4 order by userid ASC

Thank you in advance for your quick response.


0
Comment
Question by:nurv-x
  • 4
  • 2
6 Comments
 
LVL 19

Expert Comment

by:dmitryz6
ID: 17927860
use like

MS Access example

SELECT * FROM user WHERE membergroupids like *4*

sql server example

SELECT * FROM user WHERE membergroupids like %4%
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 17927864
use like

MS Access example

SELECT * FROM user WHERE membergroupids like *4* order by userid

sql server example

SELECT * FROM user WHERE membergroupids like %4% order by userid
0
 

Author Comment

by:nurv-x
ID: 17927883
That only returned records in the table that only had a 4 and nothing else in the field.

I should also mention this is php code for connection with a MySQL database.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 19

Accepted Solution

by:
dmitryz6 earned 2000 total points
ID: 17927895
SELECT * FROM user WHERE membergroupids like '%4%' order by userid
0
 

Author Comment

by:nurv-x
ID: 17927903
Thank you for your expert help! I was missing the ' ' around the percents!
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 17927914
You welcome and good luck with your project
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question