Related Data Links in SQL Editor result grids.

You can optionally enable generation of related data links in SQL Editor's query result grids. Related data are defined based on existing primary/foreign key constraints in your database schema for tables mentioned in SQL query FROM clause.

For example, if you have two tables: accounts and states, in which table accounts includes id_state column defined as a foreign key for states table and you execute the following query:

SELECT firstname, lastname, id_state FROM accounts

each cell in the result grid for id_state column will display an arrow icon right to the ID_state value. By clicking on that arrow icon, you will display the lookup values from states table for this ID value. For example, if id for state is 25 and the state name for this value is California, the in-place popup window displays California.

Another example:

Assume that table account has id_account primary key column and there are other tables that use this id_account column as a foreign key: account_orders and account_claims. When you execute the following statement in the editor:

SELECT id_account, firstname, lastname FROM accounts

each data cell for id_account column will have a link displayed. By clicking on this link, you will have a popup menu with list of related tables linked to this cell value. For example, if cell has value id_account=1500, popup menu will display the following two items:

select * from account_orders where id_account=1500
select * from account_claims where id_account=1500

By clicking on a corresponding link in the popup menu, you will see either the list of all orders for account with ID 1500 or list of all claims for account with ID 1500.

Related links significantly simplify retrieval of related lookup or dependent data without having to write and execute separate data retrieval SQL statements.

To enable generation of related data links in the result grids, check ‘Show Related Data links in results’ checkbox located in the editor status bar just above the results panel in the SQL Editor window. Once this checkbox is checked, the editor analyzes data dependencies after each SQL query execution and displays links in each column in the grid that has related data. If you check this checkbox after a result set was already retrieved and displayed in Output tab, links will be added to the existing result set grid without re-executing the SQL query. If multiple result sets were retrieved in the editor, the dependencies analysis is performed for all retrieved result sets – not only for the Output tab that is currently in focus. Cells in the grid that have related data become visually underlined and when you hover a mouse cursor over it, the arrow icon is displayed on the right side of the cell value.

Left-click on the mouse to open popup window that provides you with a list of related data links and detail data for each selected link. If more than one link is available for selected cell (as in the above example with account_orders and account_claims tables) , select the link in top list to see related data for this link in the bottom grid. The popup window is not blocking so that you can click on the link arrow in another cell of the source result grid to see related data for that cell.

You can also right-mouse click on the arrow, to display a popup menu that lists all available related links. Once you select a link the related data set is opened in new tab in the Output panel. If you choose to display all result sets in a single result tab, the new lookup data set will be added to the bottom of Results panel. Scroll Results panel to the end to see this lookup data set.

Related Data Window

Related Data Window is opened when you click on an arrow icon dispayed in a cell that has related data. This icon becomes visible when you hover a mouse over the cell or set focus on that cell. Values in all cells that do have related data links are underlined with blue dotted line.
NOTE: You need to check "Show Related Data links in results" checkbox located in the status bar above the result set panel to enable Related Data functionality. This function is not permanently enabled because it takes additional time after the query is executed to retrieve related data. Depending on your network performance and number of dependencies involved in the query, this analysis might take from a fraction of a second (in majority of cases) to several seconds.

The Related Data Window has a list of related tables (located on the top), detail area that displays data records for a table selected in the top list and buttons that allow performing several operations with the selected related data set .

Edit Data – press this button to open the selected related data set in the Data Editor component in a separate document panel. You will be able to edit existing records as well as add and delete records in the data editor document.

Open In Tab - opens the related data set in the same SQL Editor window as the source data set using a separate tab in the result set panel. If you set option Results In Single -Tab dropdown in this SQL Editor, then instead of opening a new tab, the related data set will be added as a separate grid to the bottom of Results tab that displays the source data grid as well providing you with ability to view original and related data on the page side-by-side. After you use Open in Tab command, you can use all other SQL Editor query-result related operations to work with this data set, such as copy, export, open in MS Excel, etc. You can also use Pin button above the grid to permanently pin this related result set grid so that it does not disappear when you re-execute SQL queries in the SQL Editor.

Open in SQL Editor – opens new SQL Editor with generated SQL SELECT statement that was used to retrieve the selected related data set.