Select Parent and Child Records

sql, sql-server, sql-server-2008

Solution

Use a CTE to build the company hierarchy, then join this back to the Employees table:

with CompanyHierarchy as
(
  select Id
  from Company
  where Id = 1
  union all
  select c.Id
  from Company c
    inner join CompanyHierarchy ch on c.ParentCompanyId = ch.Id
)
select e.*
from CompanyHierarchy ch
  inner join Employees e on ch.Id = e.CompanyId

SQL Fiddle with demo.

You can also substitute a `CompanyId` variable into the anchor portion of the CTE if you want to parameterize the statement:

with CompanyHierarchy as
(
  select Id
  from Company
  where Id = @CompanyId
  union all
  select c.Id
  from Company c
    inner join CompanyHierarchy ch on c.ParentCompanyId = ch.Id
)
select e.*
from CompanyHierarchy ch
  inner join Employees e on ch.Id = e.CompanyId

SQL Fiddle with demo, now with added hierarchy levels.

Problem

How do i select all the employees of a company and its child companies? Using SQL Server 2008 Employee ``` Id | Name | CompanyId ``` Company ``` Id | Name | ParentCompanyId ``` Example: 1 Microsoft 0 2 Microsoft India 1 3 Microsoft Spain 1 I have this below query which gives only employees from Microsoft and not from Microsoft India & Spain. ``` SELECT Id, Name FROM Employee WHERE CompanyId=1 ``` I am not good in SQL. Help me on this.

Original source