Select Parent and Child Records

Question

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.

Answer 1

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.

Answer 2

SELECT 
   employee.name,
   company.id 
FROM employee
INNER JOIN company 
On employee.companyId= company.id
WHERE company.id IN (1,2,3)