Is there a name for a query that returns results which have sub headings?

Question

I inhered a set of queries / macros from the previous employee in my position and one of the other people here showed me a query result (output from when the query worked correctly) which had subheadings as well as rows. They described it as a report.

In reconstructing how the series of queries worked I determined that first the rows of the employers were added to a table. Next a list of employees were added to the same table. And finally it was sorted on a hidden row which placed the employees under their subsequent employers.

This seems to me to be a very unnatural thing to do with a query...my first instinct is to place it in some other kind of report so that it loops through the employers and adds the employees below them in a sub loop; running the employee query multiple times for each employer.

Is there a name for a query like this so I can look up the technique he is using in a book? Is it an unnatural thing to do because the actual field headings don't really apply to the rows with the employer records?

Answer 1

It is a standard parent-child hierarchy. It is commonly used in recording manager/subordinate relationships among other uses. This works well in hierarchical organizations. It does not record historical relationships which need to be recorded using different techniques. It also breaks down in matrix organizations, but can still be used to record the organization structure in such cases. Project/assignment relationships need to be modeled differently.

The report structure appears to use control breaks. There are a number of query formatting tools which handle this.

There are cases where I might model employers and employees in the same table. Normally I would model them as separate entities with a join table recording the relationship.

EDIT: One of the ways to model hierarchical relationships which occur within a table is to include a parent_id column which points to the parent row. This can be an efficient method to model the relationship where there can be at most one parent, and no other data is required in the relationship. If there can be more than one parent, or there are attributes related to the relationship, then the relationship should be modeled using the join table.

Control breaks are a reporting structure where control is transferred to the outer loop when a value changes. There can be multiple levels of control breaks. When coding this manually, headers for the first row need to handled manually. These days, the complexities are usually handled by a framework or tool of some sort. Oracle's SQLPlus query tool uses BREAK ON to specify formatting related to control breaks.