How to create a complex visual Query – RightData

You are viewing archived documentation for Data Trust v2024.06 (previous version).
Go to the latest v7.6 documentation →

How to create a complex visual Query

Users can join two relevant columns using an inner and outer JOIN selection between two or more matching tables. Visual queries assist users in easy column selection and help in performing the necessary operations.

JOIN type between two matching columns has 3 selection types as shown below:

Selects all rows from both participating tables as long as there is a match between the columns. (Selected by default)
The JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a null row in the shape of the second (right) table.
The result set of the full outer join has NULL values for every column of the table that does not have a matching row. For the matching rows, the FULL OUTER JOIN produces a single row with values from columns in both tables.

To learn more, let us walk through the below procedure:

Click the ‘QUERYSTUDIO’ module.
Hover on ‘Query Builder’ snippet from the left pane options.
Click the ‘Query’ option.

The user is navigated to the Query session page.

Select ‘PostgreSQL Northwind-QA’ from the ‘Profile’ drop-down list.

Note: By default, the toggle is switched to ‘SQL Query.’

Now, switch the toggle switch to ‘Visual Query.’
Click the ‘Navigator’ button.

Select the tables Categories, suppliers, products, and order_details from the ‘Public’ schema.

Click the tables Categories, suppliers, products, and order_details to add them.

The user can view three relevant columns (categoryid, productid, and supplierid) mapped using inner and outer JOIN from the below matching tables.
Select a couple of columns other than the matching columns

Select any JOIN and right-click to view options such as ‘Delete’ and ‘Join Type.’
Click the ‘Join Type’ option.

A ‘Choose Join Type’ pop-up window is displayed.

Note: By default, the 1^st option is selected. The user can also select the other two Join Types.

Close this window.

Click the ‘Execute Now’ button.

The user is navigated to the ‘Results’ tab displaying the executed data.
Click the ‘Save’ button.

An ‘Additional savings option’ pop-up window is displayed.
Add ‘Complex Query’ in the ‘Query Name’ text field.
Click the ‘Save’ button.

A toaster message ‘Complex Query saved successfully!’ is displayed.

Note: The user can use this Query in any QC, FDR, or Validation scenarios.