Custom Queries to Database > Example: Linking Tables

Objective: Get a list of all the projects with all their data sorted by experts names, with client, project manager name and currency names.

1. Drag&drop PROJECTS, CLIENTS, CURR and AIT$USERS tables to the working area. In the PROJECTS table select “*” field (to display all table fields); select also CLIENT_NAME field in the CLIENTS table, CURR_NAME field in the CURR table and AIT$USER_REALNAME field in the AIT$USERS table.

query_example_2_1

2. In order to get the name of the client the project is assigned to, you need to set connection between PROJECTS and CLIENTS tables by unique field CLIENT_ID: click and hold left mouse button on CLIENT_ID field in PROJECTS field, drag it to CLIENT_ID field in CLIENTS table, and release the button. Similarly you need to connect the tables CLIENTS and CURR by unique CURR_ID field, PROJ_PM field of the PROJECTS table and AIT$USER_ID field of the AIT$USERS table. As a result you get the following:

query_example_2_2

3. After the request model has been created, click Generate Query button. SQL code of the request will be generated and displayed in the Content field on the Query tab.

4. You can view the results clicking Run Query button: you will get a table with a list of all the projects and their data including service data.

query_example_2_3

5. To save the request model switch to the Query tab, specify the query name and description (optionally) and click OK button.

Note: You can export or print these data from the Custom Queries tab of the Server Administrator Corporate Settings (using Export Data and Print Data buttons) (the query should be saved as described in the previous paragraph).