Sams Teach Yourself Transact-SQL in 21 Days, Second Edition

Joining a Table to Itself

Certain real-life phenomena require a database to store information about relationships within a single table. There are several examples of these types of tables. Manufacturers and parts distributors work with parts, but the parts can be gathered into kits, and the kits can often become assemblies. All those entities (parts, kits, and assemblies) are usually stored in a single table, which might be called Products. Table 5.1 lists a two-level parts and kits example.

Table 5.1. Table Containing Data in a Two-Level Structure

ItemNo	Description	PartOf
1	blue shirt	10
2	blue pants	10
3	red cape	10
4	yellow belt	10
5	red boots	10
10	Superman Suit	null

The key to the table is the PartOf column, which contains the self-referencing key. PartOf