MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 4: Rowsets

Hierarchical Rowsets

A hierarchical rowset is a collection of rowsets linked together by using chapters. Hierarchies model 1:n relationships between tables, such as a master-detail relationship between Customers, Orders, and Items. A hierarchical rowset can be stored persistently in a data source object or computed by a command. This section discusses only computed hierarchies.

Chapters are identifiers of groups of details within a rowset. Chapters work as distinct collections with a beginning and an end but also share the facilities, such as accessors and notifications, of the rowset to which they belong.

The following example illustrates how a computed hierarchy is generated. The database schema contains the following three tables:

CREATE TABLE customer_table
         ( cust_no      INTEGER,
         cust_name      VARCHAR(20),
         city           VARCHAR(20),
         PRIMARY KEY (cust_no) )

CREATE TABLE order_table
         ( cust_no      INTEGER, 
         ord_no         INTEGER,
         ord_date       DATE,
         total          DECIMAL(9,2),
         PRIMARY KEY (ord_no),
         FOREIGN KEY (cust_no) REFERENCES customer_table ) 

CREATE TABLE order_item_table
         ( ord_no       INTEGER, 
         item_no        INTEGER,
         description    VARCHAR(30),
         quantity       SMALLINT,
         unit_price     DECIMAL(6,2), 
         PRIMARY KEY (ord_no, item_no),
         FOREIGN KEY (ord_no, REFERENCES order_table )

The following extended SQL command generates a rowset hierarchy. The syntax of this query is used only for illustration. Providers may use other syntaxes to generate hierarchy.

SELECT (cust_no,
         cust_name,
         city,
         SELECT (ord_no,
               ord_date,
               total,
               SELECT (item_no,
                        description,
                        quantity,
                        unit_price,
                        (quantity * unit_price) AS ext_price 
                        )
                  FROM order_item_table
                  WHERE order_item_table.ord_no = order_table.ord_no 
                  AS item_rows
                  )
            FROM order_table
            WHERE order_table.cust_no = customer_table.cust_no 
            AS order_rows
         )
   FROM customer_table