You need to tweak a little. You could achieve it with several different method.
(1) Use VIEW
- Create a view(vw_view) of Employee and Order table like below.
SELECT dbo.Employees.EmployeeName, dbo.Employees.EmployeeID, dbo.Orders.OrderID, dbo.Orders.OrderName
FROM dbo.Employees INNER JOIN
dbo.Orders ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID
and then query like this
SELECT
v.EmployeeID,v.EmployeeName,v.OrderID,v.OrderName
FROM
vw_emp v
WHERE
v.EmployeeID BETWEEN 1 AND 40
(2) Use SubQuery
- Same above query can be used as subquery. I would recommended to use VIEW over subquery for readability and also it would help in future if some conditions will change or some table alternation will be needed.
SELECT dbo.Employees.EmployeeName, dbo.Employees.EmployeeID, dbo.Orders.OrderID, dbo.Orders.OrderName
FROM dbo.Employees INNER JOIN
dbo.Orders ON dbo.Employees.EmployeeID = dbo.Orders.EmployeeID
WHERE Employees.EmployeeID between 1 and 40
(3) Get two separate result (NOT recommended)
select Employees.EmployeeID,Employees.EmployeeName
from Employees
where Employees.EmployeeID between 1 and 40
and
select Orders.OrderID,Orders.OrderName,Orders.EmployeeID from Orders,Employees
where Orders.EmployeeID=Employees.EmployeeID
and Employees.EmployeeID between 1 and 40
You can apply filter at your business logic(in C#, VB or any other programming language you are using).
Hope it helps.