select problem

[bavs]

I am trying to create a select statement and keep running into problems

 

I have a table with columns

id int

client text

category text

 

Category contains a list of ids so examples are

 

1,3,12,16,20

12,25,17,122

23,122

12

 

I am trying to count the number of rows that contain a number in the category ie contain 12 and I expect to get 3 rows but can't get it to work

 

select count(*)

from clients

where category in ('12')

 

only returns 1 but

 

where category like '%12%' returns 4

 

I'm stuck, please help.

 

[Cook]

Frankly having a list of categories separated by commas is not the best db design you could imagine. Anyway try this one (provided there's no spaces between the cat numbers and the commas):

 

select count(*) from `clients` where `category` like '%,12,%'

[bavs]

I agree, the design is not good but I didn't do it, just trying to work with it.

 

where category like '%,12,%'

 

does not work because it doesn't pick up rows where 12 is first number or only number.

[Cook]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']select[/span] count(*) from `clients` where `category` like '%,12,%' or `category` like '12,%' or `category` like '%,12' [!--sql2--][/div][!--sql3--]