SQL Primer: Using Right Join . . . On

This example shows use of a RIGHT JOIN nested inside an INNER JOIN.

The RIGHT JOIN relates the Employees and Orders2 tables ON EmployeeID, while the INNER JOIN relates these joined tables with the Order_Details2 table based ON OrderID. The query returns the Total Sales by employee, sorted on Total Sales from highest to lowest.

LastName FirstName Title Total Units Total Sales
Buchanan Steven Sales Manager 17 $ 3002
Callahan Laura Inside Sales Coordinator 5 $ 793
King Robert Sales Representative 1 $ 310
Leverling Janet Sales Representative 1 $ 98

The SQL Source of the Recordset is as follows:

SELECT DISTINCT LastName, FirstName, Title, Sum(Quantity) AS [Total Units], Sum(UnitPrice*Quantity) AS [Total Sales] FROM (Employees RIGHT JOIN Orders2 ON Employees.EmployeeID = Orders2.EmployeeID) INNER JOIN Order_Details2 ON Orders2.OrderID = Order_Details2.OrderID GROUP BY LastName, FirstName, Title ORDER BY Sum(UnitPrice*Quantity) DESC

This Recordset includes fields from three tables: Employees, Orders2, and Order_Details2. When selecting fields from multiple tables, the fields must be referenced as tableName.fieldName if the same field appears in more than one table.

Example: Order_Details2.ProductID, Products.ProductID

This is required to explicitly specify from which table the values of conflicting fields are retrieved, and returns an error if not defined.

Go Top   |   Return to DB101 ADO Samples