Query the number of times a number occures in a record amongst other numbers.

[pioneerx01]

The title is a bit confusing so let me explain. Lets say that I have users select 5 numbers from 1 to 100 (non repeating) and the results are stored in the databse unde columns called name and numbers. So Jack can pick 3, 6, 10, 56, 63 while Bob picks 10, 36, 69, 70, 99. They are stored as shown, seperated with commas in the databse.

 

Is there a simpleway of writing a query that finds who many have selected the number 10. I have tried to use various combinations of LIKE with quering, but is not always 100% acccurate. I have resorted to querying all records, then exploding the numbers and counting then that way. I am looking for something on the lines of:

 

SELECT * FROM table WHERE numbers CONTAINS '10'

 

I know CONTAINS in not valid.

 

Thanks

[mac_gyver]

you need to normalize your data so that you can find and manipulate the data easily. basically, there would be one row in a table for each piece of data that table is designed to hold.

 

you would have two tables - users, and picks

 

 the users table -
 user_id    name
 2          jack
 13         bob

 
 the picks table -
 user_id pick
 2       3
 2       6
 2       10
 2       56
 2       63
 13      10
 13      36
 13      69
 13      70
 13      99
 
 the query to get the answer to the question of how many have selected the number 10 -
 
 select count(*) from your table where pick = 10