Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

JOINs in MS SQL Server

Introduction
3. Filtering self joined tables
Dictionary database
Summary

Instruction

Great! When self-joining tables, we can still filter the results with the WHERE clause, just as we can when JOINing two different tables.

Suppose that we want to show each employee's name along with the name of their supervisor for only those employees who have less than five years of experience:

SELECT
  E.Name AS EmployeeName,
  S.Name AS SupervisorName
FROM Employee AS E
JOIN Employee AS S
  ON E.SupervisorId = S.Id
WHERE E.Experience < 5

Note that we always have to specify the table name (an alias) before a given column when self-joining. Otherwise, the column Name would be ambiguous, as there would be two columns with the same name: one from the Employee table, and one from the Supervisor table.

Exercise

Show only worker's name, and specialization. Alias column with the name as Apprentice. Consider only those workers whose supervisors have more than 14 years of experience.

Stuck? Here's a hint!

Type:

SELECT
  Apprentice.Name Apprentice,
  Apprentice.Specialization
FROM WorkshopWorkers Apprentice
JOIN WorkshopWorkers Master
  ON Apprentice.MasterId = Master.Id
WHERE Master.Experience > 14