[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

Get Subcategories count from Category table in T-SQL

Hello Dear Coders,

 

I have a table which i save my all categories info with Parent Category Info. Let's go on a sample.

 

CatID           CatName              MainCatID

-------------------------------------------------------------

1                  Electronics             0

2                  Cell Phones            1

3                  PDAs                     1

4                  Nokia                     2

5                  N73                       4

6                  Sony Ericsoon        2

7                  K700i                     6

.

.

.

 

 

Now,

 

As you guess MainCatID = 0 cats are Main Cats.

 

I need to list Electronics Sub Categories in SQL with a simple query. I don't want to use Cursor or Table (Not TempTable).

 

something like Select CatID from Categories Where MainCatID In (1,2,4,6)  -- How can find the elecktronic sub categories?

 

Thanks in advance.
0
Dhanasekaran Sengodan
Asked:
Dhanasekaran Sengodan
  • 2
2 Solutions
 
seenuforallCommented:
Select CatId from categories(sub categroy table) a, category(maincategory table) b
where a. MainCatID = b.MainCatID

0
 
seenuforallCommented:
select  Maincatid, count(*) from categroy group by MainCatid
0
 
Dhanasekaran SengodanAuthor Commented:
i need output like this


CatID                  count
----------------------------
1                            6
2                            4
3                            0
4                            1
5                            0
6                            1
and so on ..............
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now