What does this SQL code (joining on the same view twice) do?

Question

I'm working my way through this explanation of Common Table Expressions at MSDN and I came across the code reproduced below. Can anyone explain to me what it does and how it does it?

I'm pretty sure I understand the concept of the view that is created in the first half. But the SELECT that joins on that view twice is confusing me.

CREATE VIEW vwMyView AS
SELECT EmployeeID, COUNT(*) AS NumOrders,  MAX(OrderDate) AS MaxDate
FROM Orders
GROUP BY EmployeeID

---

SELECT 
    e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID,
    om.NumOrders, om.MaxDate
FROM 
    Employees AS e
    INNER JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID
    INNER JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID

Answer 1

The employee table has a "ReportsTo" column which will be the ID of the employee's manager. By joining twice (once as oe and once as om), the SELECT statement will give you the NumOrders and MaxDate for each employee followed by those of their manager's.

Answer 2

It is a query to a self join table.

Basically the table will store values from all employees, including a self reference to the person they report to.

Thus, the view is used to join to the table Employees to retrieve the data for a given employee, and then also the data of the person they report to.

Answer 3

The key to understanding the following:

FROM EMPLOYEES e
JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID
JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID

...is the JOIN criteria. The oe instance is a copy tied on by the e.employeeid; the om instance is tied on using the e.reportsto.

Answer 4

The first join gets information about the employee and the second join gets information about that employee's manager (who is also an employee).

Answer 5

It allows you to join "om.NumOrders" and "om.MaxDate" into each returned row where "om.EmployeeID" matches "e.ReportsTo" or put another way, includes two columns showing [NumOrders] and [MaxDate] for whoever "e.EmployeeID" 'reports to' (e.g. manager tallies next to the employee data)

Why? I wouldn't know, I'm neither familiar with the data, example or docs you referred to. But that's what that join does.

The purpose of the view is to generate those aggregate values (NumOrders and MaxDate) for every employee.