O'Reilly logo

Microsoft® SQL Server™ 2000 Programming by Example by Carlos Eduardo Rojas, Fernando G. Guerrero

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Self Joins

A self join is a special type of join, in which a certain table is joined to itself. Basically, in a self join, two copies of the same table are merged, generating a result set based on information stored in this table.

Generally, self joins are used to represent hierarchies in a table. For example, the Employees table has a column called reportsto, which has a foreign key pointing to the employeeid column in this table. Therefore, if you want to retrieve the manager of any employee, the Employees table must be joined to itself.

Listing 5.20 demonstrates how to extract information from this hierarchy represented in the Employees table, using a self join. Specifically, the query performs a self join to retrieve the name of Anne Dodsworth's ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required