Jump to content


Photo

select problem


  • Please log in to reply
3 replies to this topic

#1 bavs

bavs
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 09 November 2005 - 07:44 AM

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.


#2 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 09 November 2005 - 07:48 AM

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,%'

Cook

#3 bavs

bavs
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 09 November 2005 - 08:15 AM

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.

#4 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 09 November 2005 - 08:25 AM

[!--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--]
Cook




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users