Find all Subordinates - SQL :)

Sometimes we need to drill down till the end to find out all the infos. i had a quick look at the problem and find there is nothing to worry until you have the Common table expression thing for SQL.

Unfortunately if you want to write down the LINQ for this you cant do it in a single shot. Although you can use Range in LINQ.

DECLARE @Id int = '1234';
WITH identity_cte AS (SELECT EMP_NO
                FROM IdentitySQL
                WHERE MANAGER_EMP_NO = @id
                UNION ALL
                SELECT Id1.EMP_NO
                FROM IdentitySQL Id1 
                INNER JOIN identity_cte icte ON icte.EMP_NO = Id1.MANAGER_EMP_NO) 
SELECT * FROM identity_cte 
union all 
select emp_no from identitysql where emp_no = @id

This will return all subordinates including the Manager himself.