System and method for configuring, sequencing and viewing joins in a query

A software tool for creating SQL queries involving tables, wherein the tables include columns, wherein the columns of two different tables are capable of having relationships between the columns, comprising:

• a graphical user interface for selecting at least three tables and for defining relationships between the three tables;
• and
• an advanced joins viewer for depicting one or more intermediate result sets comprising two or more of the selected tables, and for configuring the join types between the tables independently of any defined relationships;
• wherein the join type is an inner join type or an outer join type;
• wherein the advanced joins viewer is operable to configure a join type between two of the tables such that the configured join type is independent of a relationship involving the tables' columns and is capable resolving an ambiguity that is capable of arising due to a relationship involving the table's columns.

A method of configuring joins in an SQL query involving tables wherein the tables include columns, wherein the columns of two different tables are capable of having relationships between the columns, comprising the steps of:

• adding two or more tables to the query;
• setting column relationships between the two or more tables;
• selecting an intermediate result set from the two or more tables added to the query;
• displaying the join type and column relationships for the selected intermediate result set;
• and
• configuring the join type for the intermediate result set independently of the column relationships;
• wherein the join type is an innerjoin type or an outer join type;
• wherein the configured join type exists between two of the tables such that the configured join type is independent of a relationship involving, the tables' columns and is capable of resolving an ambiguity that is capable of arising, due to a relationship involving the table's columns.

A method of configuring joins in a database management system, comprising the steps of:

• selecting a plurality of tables from a relational database;
• setting a plurality of relationships between the plurality of tables;
• wherein the relationships set between the plurality of tables are join relationships between columnns of the tables;
• displaying a sequenced listing of the selected tables;
• selecting an intermediate result set comprising two or more of the selected tables;
• displaying a plurality of join types for the intermediate result set;
• and
• selecting a join type for the intermediate result set independently from the plurality of relationships;
• wherein the join type for the intermediate result set is selected independently of a join relationship involving the tables' columns;
• wherein the selected join type is an innerjoin type or an outer join type.

A method of automatically sequencing tables joined in an SQL query, comprising the steps of:

• (a) providing a join object for each table, wherein the join object includes a table relationships list that sets forth related tables;
• (b) providing a first list of the tables in the SQL query;
• (c) moving a first table from the first list into a second list and deleting the first table from the first list;
• (d) determining whether the first table has a related table in its table relationships list, and, if so, then moving the related table from the first list to the second list and deleting the related table from the first list;
• (e) repeating step (d) until a selected table has no related table in its table relationships list;
• (f) fetching a second table from the first list;
• (g) determining whether the second table has a related table in its table relationships list that has been moved to the second list, and, if so, then moving the related table from the first list to the second list and deleting the related table from the first list;
• and
• (h) repeating steps (d) through (g) until there are no tables remaining in the first list.

A computer-implemented process for sequencing and configuring joins in an SQL query, comprising the steps of:

• graphically building an SQL query having a plurality of tables, wherein the tables are related by a plurality of table relationships;
• wherein the relationships set between the plurality of tables are join relationships between columns of the tables;
• automatically sequencing the tables in order to generate a valid SQL query;
• graphically depicting the sequencing of the tables as a plurality of intermediate result sets;
• selecting one of the intermediate result sets;
• and
• configuring the join type for the selected intermediate result set independently from the join relationships between the columns of the tables,
• wherein the configured join type is an inner join type or an outer join type.

A method of visually depicting and modifying the sequence of joins in a query, comprising the steps of:

• generating a sequence display window comprising a plurality of tiles, wherein each tile corresponds to a table in the query that is joined to some other table;
• ordering the tiles in a particular sequence;
• selecting one of the tiles to be re-ordered in the sequence;
• graphically moving the selected tile from a first position in the sequence to a second position in the sequence;
• and
• re-ordering the tiles so that the selected tile is depicted in the second position in the sequence.

A method for configuring, sequencing and displaying joins in a query involving tables, wherein the tables include columns, wherein the columns of two different tables are capable of having join relationships between the columns, comprising the steps of:

• (a) displaying a sequence display window for depicting the sequence in which a plurality of tables are joined in the query, and for selecting an intermediate result set comprising two or more of the depicted tables;
• and
• (b) displaying through a join configuration and display window the selected intermediate result set, including a table display for depicting the tables associated with the intermediate result set, a join type selector for configuring the type of join for the intermediate result set, and a relationships display window for depicting one or more relationships between the tables in the intermediate result set,
• wherein step (a) is performed prior to step (b),
• wherein the join type selector selects from a list of join types, wherein the list comprises at least two of the following join types;
• an inner join type, a left outer join type, a right outer join type, and a full outer join type;
• wherein the join configuration and display window configures join types between the tables independently of relationships between the tables such that the join configuration and display window is operable to configure a join type independent of a relationship involving the tables' columns.