SQL – Select Parent and Count Child from same table


//Let's say, you have a table as follows-
--------------------------------------------
 DesignationId | DesignationName| ParentId |
 -------------------------------------------
    1          |     A          |    0     |
 -------------------------------------------
    2          |     B          |    1     |
 -------------------------------------------
    3          |     C          |    1     |
 -------------------------------------------


// You would like to select records as follows
--------------------------------------------
 DesignationId | DesignationName| Childs   |
 -------------------------------------------
    1          |     A          |    2     |
 -------------------------------------------
    2          |     B          |    0     |
 -------------------------------------------
    3          |     C          |    0     |
 -------------------------------------------

Here is the SQL Statement-

SELECT DesignationId AS Id, DesignationName as Name, 
(SELECT COUNT(*)  FROM Designations AS T WHERE T.ParentID = O.DesignationId) 
FROM Designations AS O Where O.DesignationId>0
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s