display the number of results found for a subcategory

Hello,

I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:

The user can make a selection from the categories listed below:

Geographic Area
Time Period
Topic
Record Type


If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:

Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)


Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.

Thanks

You have to run a SQL statement for each sub-category:
SELECT COUNT(*) FROM...
with the rest being exactly what you use for a SQL statement on the next page.

Hope that helps.

actually, running one query for all sub-categories is better than running a query for each subcategory

Thanks to both of you for your help. I have used the query you provided as a starting point. here is my query:


Select record_name, count(record_name)as rc_count from record_type, vv_entry
where record_name *= vv_entry.record_type
group by record_name


These are the results that I am getting:

record_name rc_count
Audio Recording 1
Document 10
Map 2
Microfilm 1
Other 1
Photograph 5
Plan 1
Video Recording 1
Visual Image 4


The problem that I have now is that the rc_count values that are 1 should be 0. Is there any way that I can use an If statement that will change all of the 1s to 0s?

Your help is greatly appreciated

Thanks to both of you for your help. I have used the query you provided as a starting point. here is my query:


Select record_name, count(record_name)as rc_count from record_type, vv_entry
where record_name *= vv_entry.record_type
group by record_name


These are the results that I am getting:

record_name rc_count
Audio Recording 1
Document 10
Map 2
Microfilm 1
Other 1
Photograph 5
Plan 1
Video Recording 1
Visual Image 4


The problem that I have now is that the rc_count values that are 1 should be 0. Is there any way that I can use an If statement that will change all of the 1s to 0s?

Your help is greatly appreciated

Thank you very much. This worked perfectly. Now, I am going to research the left joins.

Thanks again