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

• US 6,640,221 B1
• Filed: 07/10/2000
• Issued: 10/28/2003
• Est. Priority Date: 07/10/2000
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Giles, Steven, Boozer, John Forrest, Levine, Fred
• Primary Examiner(s): Shah, Sanjiv
• Application Number:
  US09/613,348
• Time in Patent Office: 1,205 Days
• Field of Search: 707/3, 707/2, 707/1, 707/4, 707/5, 707/6, 707/101, 707/104
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06F 16/2425 Iterative querying; Query formulation based on the results of a preceding query
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

A system and method for configuring, sequencing and viewing joins in a query is provided. The system includes an advanced joins viewer that may be invoked from an SQL Query Tool. A graphical user interface associated with the advanced joins viewer displays a sequenced vertical tile list of each table and each intermediate result set in a particular query. Through this interface, a user may re-sequence the ordering of the tables and thus reconfigure the intermediate result sets that are generated by the query. The advanced joins viewer enables the independent configuration of the join type and the join relationships for each of the intermediate result sets. An automatic sequencing algorithm is also provided for configuring the ordering of the tables such that the query produces valid SQL.

Claims

1. A system for configuring, sequencing and displaying joins in a query, comprising:

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

a join configuration and display window for displaying 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 the join type selector selects from a list of join types, wherein the list of join types comprises an inner join type and an outer join type;

wherein the tables include columns, wherein the columns of two different tables are capable of having a join relationship between the columns;

wherein the join type selector configures a join type two of the tables independently of a join relationship involving the tables'"'"' columns.

2. The system of claim 1, further comprising:

an automated sequencing algorithm that reconfigures the sequence of the plurality of tables so that the resulting query is valid.

3. The system of claim 2, further comprising a plurality of join objects for each of the plurality of tables in the query, wherein each join object for a given table includes:

an identification of the table;

one or more join relationships that define the criteria for joining the table to other tables;

an indication of the type of join for the table; and

a table relationships list that indicates what other tables are related to the table.

4. The system of claim 1, wherein the sequence display window depicts the plurality of tables as a vertical tile list, wherein each tile in the list corresponds to a table.

5. The system of claim 4, wherein the vertical tile list is sequenced from top to bottom.

6. The system of claim 1, wherein the join type selector includes a plurality of radio buttons, each radio button being associated with a type of join.

7. The system of claim 6, wherein the type of join includes an inner join and one or more types of outer joins.

8. The system of claim 1, wherein the table display further comprises a join type indicator that links the tables in the intermediate result set, wherein the join type indicator graphically depicts the type of join for the selected intermediate result set.

9. 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.

10. The software tool of claim 9, wherein the advanced joins viewer includes:

a sequence display window for graphical depicting the order of the tables and the intermediate results sets that form the query; and

an intermediate result set join display window for selecting the join type of an intermediate result set independent from any of the defined relationships;

wherein the ambiguity is capable of arising due to a join type not being established for a relationship between columns from two tables.

11. The software tool of claim 10, wherein the intermediate result set join display window includes a table display that displays the tables associated with a selected intermediate result set, a join setting window for selecting a join type for the selected intermediate result set, and a relationships window for displaying the column relationships between the tables in the intermediate result set.

12. The software tool of claim 10, further comprising:

means for graphically re-sequencing the tables in the query.

13. The software tool of claim 12, further comprising:

means for determining whether the re-sequenced query will generate valid query syntax; and

if the re-sequenced query will not generate valid query syntax, then automatically re-sequencing the order of the tables so as to generate valid query syntax.

14. 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.

15. The method of claim 14, further comprising the step of:

automatically sequencing the two or more tables each time a column relationship is set.

16. The method of claim 14, further comprising the step of:

displaying the order in which the two or more tables are joined in the query using a vertical tile list.

17. The method of claim 16, further comprising the step of:

manually re-sequencing the two or more tables by graphically selecting one of the displayed tables and moving it to another location in the vertical tile list.

18. The method of claim 17, further comprising the steps of:

determining whether the re-sequenced tables will generate valid SQL, and if not, then automatically re-sequencing the tables so to generate valid SQL.

19. 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.

20. 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.

21. 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.

22. 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.

23. 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.

24. The method of claim 23, further comprising the step of:

using an automated sequencing algorithm that reconfigures the sequence of the plurality of tables so that the resulting query is valid.