1

I have following table structure and data in MySQL

TABLE NAME : categories

CatID  CatName     ParentCatID
-------------------------------
1       Shirts        NULL
2       Short Sleev    1
3       Long Sleev     1
4       Collarless     2
5       Collar         2
6       Collarless     3
7       Collar         3
8       Square Cut     4
9       Round Cut      4
10      Square Cut     6
11      Round Cut      6

table name : companies
-------------------------------------------------
companyid |   company name | country | categoryid
-------------------------------------------------
1         |  Unitile ltd.  | India   |  5
2         |  abc ltd.      | India   |  2
3         |  infocom ltd.  | India   |  1
4         |  tata ltd.     | India   |  5
5         |  agro india    | India   |  1

I have 2 level of categories like : Shirts > Long Sleev

I want total records count under parent category like Shirts

ajreal
  • 46,720
  • 11
  • 89
  • 119
kumayan
  • 41
  • 1
  • 3
  • 1
    Where is the question/problem? You just wrote done the fact, that you want the total record count. – Thomas Berger Aug 19 '11 at 10:05
  • sound like a hierarchical querys needed to me ? see this SO question for guidance http://stackoverflow.com/questions/2782525/retrieving-data-with-a-hierarchical-structure-in-mysql – Kevin Burton Aug 19 '11 at 10:24

1 Answers1

0

I don't understand what the companies table has to do with the problem. Something like this may do what you may be trying to achieive

SELECT 
C.CatID, COUNT(DISTINCT (IFNULL(PC.CatID, 0))) AS parentcategories
FROM categories C
LEFT JOIN categories PC ON (C.ParentCatID = PC.CatID)
GROUP BY C.CatID
;
Harald
  • 131
  • 5