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

[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

[Marc Gravell]

Re: Sql Query for finding count of records which have say firstId=1 and SecondId=1

Not quite sure how this relates to dotnet...

So is there any solution to this problem?

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