Creating Relationships Between Objects

About Join Conditions

When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition. A join condition determines how the rows from one object will combine with the rows from another object.

Query Builder supports inner, outer, left, and right joins. An inner join (also called a simple join) returns the rows that satisfy the join condition. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Joining Objects Manually

You can create a join manually by selecting the Join column in the Design pane.

To join two objects manually:

1. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

2. From the Object Selection pane, select the objects you want to join.

   The objects display in the Design pane.

3. Identify the columns you want to join.

   You create a join by selecting the Join column adjacent to the column name. The Join column displays to the right of the datatype, beneath the Remove icon. When your cursor is in the appropriate position, the following help tip displays:

   Click here to select column for join
   
   

4. Select the appropriate Join column for the first object.

   When selected, the Join column displays as a dark gray. To deselect a Join column, simply select is again or press ESC.

5. Select the appropriate Join column for the second object.

   
   

   Tip: You can also join two objects by dragging and dropping. Select a column in the first table and then drag and drop it onto a column in another table.

   
   

   When joined, a green line connects the two columns.

6. Select the columns to be included in your query. You can view the SQL statement resulting from the join by positioning the cursor over the green line.

7. Click Run to execute the query.

   The Results pane displays the query results.

Joining Objects Automatically

When you join objects automatically, the Query Builder suggests logical, existing parent and child relationships between existing columns.

To join objects automatically:

1. Navigate to Query Builder:

   1. Click the SQL Workshop icon on the Workspace home page.

   2. Click Query Builder.

      Query Builder appears.

2. From the Object Selection pane, select an object.

   The object displays in the Design pane.

3. Click the small icon in the upper left corner of the object. Depending upon the selected object, the icon label displays as Table Actions or View Actions.

   The Actions window appears. Use the Actions window to select all columns within the current object or objects related to the current object.

4. In the Actions window, select the appropriate options:

   • Check All - Select this option to select the first twenty columns in the current object.

   • Add Parent - Displays tables that are referenced as a foreign key to the current object.

   • Add Child - Displays tables that reference the current object in a foreign key.

   If using Add Parent or Add child, the selected object appears and a green line connects the foreign key columns.

5. Select additional columns to be included in your query.

   You can view the SQL statement resulting from the join by positioning the cursor over the green line.

6. Click Run to execute the query.

   The Results pane displays the query results.