Browse Prior Art Database

A Graphical User Interface for Specifying Complex Nested Join Conditions in a SQL Query Disclosure Number: IPCOM000131066D
Original Publication Date: 2005-Nov-07
Included in the Prior Art Database: 2005-Nov-07
Document File: 4 page(s) / 247K

Publishing Venue



Disclosed is a technique for assisting a user of a SQL database in the task of creating complex table joins in SQL queries. The technique involves an interactive dialog where available tables and existing joins are shown in a tree structure, allowing the user to combine tables to create new joins in a way that makes the join precedence explicit. Another portion of the dialog allows the user to define the join conditions. When the available tables have relational constraints between them defined in the database, the constraints are used to automatically generate the join conditions.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 39% of the total text.

Page 1 of 4

A Graphical User Interface for Specifying Complex Nested Join Conditions in a SQL Query

Interactive query builder tools, such as Microsoft Access, provide assistance in creating SQL queries. This includes giving the user a certain amount of assistance in defining "joins" between source tables included in a query. However, as the number of tables and joins included in the query increases, the assistance provided by these tools becomes less and less effective. That is, the tools do not "scale" well when the number or complexity of joins increases, and they do not handle nested joins well (or at all). This invention provides a means for creating, displaying, and manipulating table joins of arbitrary complexity.

This invention involves a "join dialog" where join information is displayed, with controls that allow a user to create new joins and modify existing ones. The join dialog consists of two main areas: the 'joins' area and the 'details' area. The item or items selected in the 'joins' area affect the contents of the 'details' area. The details area gives users information regarding the selected item or items, and allows certain modifications to the item's content.

Upon launch, the 'joins' area lists all tables available in the FROM clause of the query. The first table is highlighted by default. The 'joins' area is a prefix binary tree representation of join operations. It has several functions:

It shows the join relationships between tables and the precedence of those joins It suggests potential join candidate tables for the currently selected table or tables It indicates unjoined tables or unconnected join paths It abstracts join operations from the SQL syntax; users need not concern themselves whether the tables are joined using the ON clause or the WHERE clause

The 'joins' area is used as follows: users select at least two tables or joined tables. As more items are selected, the interface uses a join discovery algorithm, to suggest potential join candidates for the currently selected items. The selection order is factored into this decision. The algorithm works as follows:

An unselected table is a table in the FROM clause which has not been selected by the user in the 'joins' area. Selecting a join node, recursively includes all contained tables in the list of selected tables.

For every unselected table that can be joined to one or more of the tables contained in the list of selected tables:

If it is unjoined (i.e. root items), then set the icon to a candidate table icon If it is joined, then recursively find the root of the join path in which the unselected table belongs. Make sure that this root is different from the root of any of the selected tables. NOTE: The root of an unjoined table is the table itself.

Provided that the root satisfies the above condition, change the icon of the unselected table, as well as all of its parent nodes all the way to the root, to either a candidate table (for the table itself) or a candidate...