Sql Query for finding count of records which have say firstId=1 andSecondId=1

  • Thread starter Thread starter Chetana
  • Start date Start date
C

Chetana

Hi All,
I want the query in sql which gives count of records for 2 conditions.
Say i have table Category which has CategoryID and SubCatID as 2
columns.

Then suppose i want count for the records which has CategoryID=1 and
SubCatID =1 .
But I have SubCatID field as comma separated means it may has 1,2,3 or
1 or 11,2,3
I have used query like

select count(*) from Category where CategoryID=1 and SubCatID like
'%1%'

but it take 11 as subcatid which i donot want .
So is there any solution to this problem?

Thanks
 
Not quite sure how this relates to dotnet...
So is there any solution to this problem?
Click to expand...

How about: don't store relational data as csv if you want to query it? Or
more usefully, how about:

WHERE ',' + SubCatId + ',' LIKE '%,1,%'

Obviously this is simpler if your csv starts and ends with ',' as you don't
need the extra concatenation (on the left-hand-side). However! This approach
should only be used if your CategoryID can limit the results fairly well;
LIKE with an open start can't really use the index (especially since we are
using a computed [concatenated] value, not the raw column value).

Personally I'd be inclined to store the data out into another table, or
(perhaps) use sqlxml which provides /a/ mechanism (perhaps not the best) to
store multiple values in a single field while retaining the ability to query
and index.

Marc
 
Back
Top