When you want to access data from more than one table at a time, you must first describe the relationships between those tables. The definitions of these relationships are called joins. Joins identify the columns in each table that are related as well as the operator (such as the equal sign, =) that defines how they are related. When you add a join to your query and retrieve data from related tables, each row of data in the resulting report contains information taken from all of the related tables.
For example, before you can query information that is stored across the two tables REPORTER_STATUS and REP_SEVERITY_TYPES, you need to create a join that expresses the relationship between the two tables. In this case, you can connect the column ORIGINALSEVERITY in the table REPORTER_STATUS to the column SEVERITY in REP_SEVERITY_TYPES using the equal sign (=) operator.
It is common for the columns used in a join to have similar or identical names in the different tables.
If you view the SQL generated by the Report Designer you can see the join definitions added to the WHERE clause of the query. You should not change or remove the joins that have been added unless you are familiar with SQL and the design of your database.
The Report Designer Joins tab is shown below.
|
|
Figure 68: Joins Tab |
You must add all required joins before you run a query to ensure the query returns meaningful data. If you are not sure about which joins to add, contact your database administrator.
Current joins, or joins that have already been defined, are displayed in the Joins: list. To add a new join, select the two tables you want from the two Table: drop-down lists. The columns that are part of each table appear in the Columns: list. Highlight the column in each list you want to use. Almost all common joins use the equal sign (=). If you want to use another operator, select it from the drop-down list. Click the Add Join or Add Outer Join button to move your selection to the Joins: list on the top of the tab. For a description of outer joins, see Outer Joins on page 136.
You can edit an existing join by highlighting that join in the Joins: list and clicking the Edit Join button. If you want to remove a line from the Joins: list, click the line to highlight it and click the Delete Join button.
Report Designer can interrogate the database to see if the database contains information on joining the two tables.
Select the two tables you want to join and click the Auto Join button. If Report Designer can automatically locate the join, it is added to the Joins: list, and the message Auto Join done appears at the bottom left of the tab. If no joins are found then a message to that effect appears and you must manually define the join.
When you are defining joins you usually want the returned data to obey all of the relationships that you have described in your joins. In that case, always leave the AND radio button selected. This is suitable for most database queries.
If you are aware of special cases in your database, you can use the OR button to create more complex connections between the joins that you are defining. The AND and OR buttons represent logical operators that describe how the different parts of a complex join are connected. If you need more information on the use of the AND and OR logical operators, refer to your database reference manual or contact your database administrator.
An outer join is a special kind of join that extends the query to return data that would otherwise be omitted by a standard join. An outer join includes, in addition to the data returned by an inner join, the data from rows in a table which do not have matching rows in another table in the query.
For example, the tables REP_AUDIT_ACK and REP_AUDIT_SEVERITY can be joined using the column ENDDATE. In some types of event this column is not populated or contains a null value. A query created using this join cannot return data on these rows. If you want to create a query that returns all rows, regardless of whether the values in ENDDATE match, you must use an outer join.
Selecting an outer join tells Netcool Reporter to extend each table in the join by adding a row of data that matches any unmatched row from the other table. For a more complete explanation of how outer joins work in your database, refer to your database reference documentation.
To create an outer join, select the tables and columns just as you would for an inner join and click the Add Outer Join button.
Not all relational databases support the outer join construct. Ask your database administrator if you are not sure whether your database supports the outer join construct.