If the DataWindow object will contain data from more than one table, you should join the tables on their common columns. If you have selected more than one table, PowerBuilder joins columns according to whether they have a key relationship:
-
Columns with a primary/foreign key relationship are joined automatically.
-
Columns with no key relationship are joined, if possible, based on common column names and types.
PowerBuilder links joined tables in the SQL Select painter Table Layout view. PowerBuilder joins can differ depending on the order in which you select the tables, and sometimes the PowerBuilder best-guess join is incorrect, so you may need to delete a join and manually define a join.
To delete a join
-
Click the join operator connecting the tables.
The Join dialog box displays.
-
Click Delete.
To join tables
-
Click the Join button in the PainterBar.
-
Click the columns on which you want to join the tables.
To create a join other than an equality join, click the join operator in the Table Layout view.
The Join dialog box displays:
-
Select the join operator you want and click OK.
If your DBMS supports outer joins, outer join options also display in the Join dialog box.
All PowerBuilder database interfaces provide support for ANSI SQL-92 outer join SQL syntax generation. PowerBuilder supports both left and right outer joins in graphics mode in the SQL Select painter, and full outer and inner joins in syntax mode. Depending on your database interface, you might need to set the OJSyntax DBParm to enable ANSI outer joins. For more information, see the section called “OJSyntax” in Connection Reference.
The syntax for ANSI outer joins is generated according to the following BNF (Backus Naur form):
OUTER-join ::=
table-reference {LEFT | RIGHT} OUTER JOIN table-reference ON search-condition
table-reference ::=
table_view_name [correlation_name] | OUTER-joinOrder of evaluation and nesting
In ANSI SQL-92, when nesting joins, the result of the first outer join (determined by order of ON conditions) is the operand of the outer join that follows it. In PowerBuilder, an outer join is considered to be nested if the table-reference on the left of the JOIN has been used before within the same outer join nested sequence.
The order of evaluation for ANSI syntax nested outer joins is determined by the order of the ON search conditions. This means that you must create the outer joins in the intended evaluation order and add nested outer joins to the end of the existing sequence, so that the second table-reference in the outer join BNF above will always be a table_view_name.
Nesting example
For example, if you create a left outer join between a column in Table1 and a column in Table2, then join the column in Table2 to a column in Table3, the product of the outer join between Table1 and Table2 is the operand for the outer join with Table3.
For standard database connections, the default generated syntax encloses the outer joins in escape notation {oj ...} that is parsed by the driver and replaced with DBMS-specific grammar:
SELECT Table1.col1, Table2.col1, Table3.col1
FROM {oj {oj Table1 LEFT OUTER JOIN Table2 ON Table1.col1 = Table2.col1}
LEFT OUTER JOIN Table3 ON Table2.col1 = Table3.col1} Table references
Table references are considered equal when the table names are equal and there is either no alias (correlation name) or the same alias for both. Reusing the operand on the right is not allowed, because ANSI does not allow referencing the table_view_name twice in the same statement without an alias.
Determining left and right outer joins
When you create a join condition, the table you select first in the painter is the left operand of the outer join. The table that you select second is the right operand. The condition you select from the Joins dialog box determines whether the join is a left or right outer join.
For example, suppose you select the dept_id column in the employee table, then select the dept_id column in the department table, then choose the following condition:
employee.dept_id = department.dept_id and rows from department that have no employee
The syntax generated is:
SELECT employee.dept_id, department.dept_id
FROM {oj "employee" RIGHT OUTER JOIN "department" ON "employee"."dept_id" = "department"."dept_id"}If you select the condition, rows from employee that have no department, you create a left outer join instead.
Equivalent statements
The syntax generated when you select table A then table B and create a left outer join is equivalent to the syntax generated when you select table B then table A and create a right outer join.
For more about outer joins, see your DBMS documentation.
