Adding and joining tables in the Query Builder editor

To create a visual query in the Query Builder editor, you must add tables first.

1. From the main menu, select File > New > Visual Query. The Create New Visual Query wizard opens. Specify the name of the query in the Name field and the data source that you want to use for the query in the Data Source field. Click Finish.
2. Click the Build tab. The Query Builder editor opens. The Build tab is divided into three sections: Tables, Fields, and SQL Editor.
   Tip: Expand and collapse the sections by clicking buttons next to the section names to make all functions of a particular section available.
3. To add a table to the query:
   1. Click the Plus button.
   2. In the Tables window, specify the table owner and the table name or click Add from List... to select a table or several tables from a specified list.
   3. Click Add.
   You can view the added tables in the Tables section. If the specified data source contains ER diagrams, you can view the tables from the ER diagrams in the Suggested Tables group. If there are no available ER diagrams, the Suggested Tables group will contain all the tables that are stored under the table owner node from which you added the tables.
4. To add a table from Suggested Tables, double-click it.
   Note: To view a long list of items, use the scroll bar. If the number of items in the Suggested Tables list exceeds 500, the tables cannot be viewed.
5. To view the available ER diagrams from the specified data source and add or remove fields, click the ER Diagrams button.
6. To remove a table from the query, navigate to the table that you want to remove and click Close.
7. To add a new SQL statement to the query, click in the toolbar.
8. To remove the SQL statement from the query, click toolbar button.
9. To sort columns in the table, navigate to the table the columns of which you want to sort and click the sorting button. You can also clear the Show All Columns check box to hide the columns that are not included in the query.
10. If you add several tables to your query, the Join Tables dialog opens. In the dialog, specify the joining option for the tables in your query. For more information about table joining options, see the Joining tables topic.
    Note: The Join Tables dialog appears only if the tables in your query are not joined in the data source ER Diagrams.
11. To specify relationships among several tables and join tables, select a column from one table and drag it to a column of another table. You can set up multiple join conditions. If the Tables section is expanded, a connecting line is drawn from the column in one table to the column in the second one.
    Restriction: You cannot join database tables and accelerator tables in one query. You cannot join tables that are stored in different accelerators either.
12. Optional: To edit the type of join conditions, in the expanded Tables section, right-click the join line that appears between any two tables. Select Change Join from the menu to change the join conditions. The Join Tables window opens. You can select new join conditions. For detailed information about types of join conditions, see the step 2 in the Editing join conditions for multiple tables topic.
13. To remove the join condition, right-click the join that you want to delete and select Remove Join.