This article describes how to use subquery in where clause.
SELECT Title,EmployeeID,NationalIDNumber,ManagerID
FROM dbo.HumanResources_Employee WHERE EmployeeID IN
(SELECT EmployeeID FROMdbo.HumanResources_EmployeeDepartmentHistory WHERE DepartmentID
IN (SELECT DepartmentID FROMdbo.HumanResources_Department WHERE GroupName IN (‘Research and Development’,‘Executive General and Administration’)))
SELECT Title,EmployeeID,NationalIDNumber,ManagerID
FROM dbo.HumanResources_Employee WHERE EmployeeID IN
(SELECT EmployeeID FROMdbo.HumanResources_EmployeeDepartmentHistory
WHERE DepartmentID
IN (SELECT top 5 DepartmentID FROMdbo.HumanResources_Department ))
order by ManagerID
