This feature is probably the most advanced, impressive, and useful !

Use cases

  • Compare 2 databases to check their integrity
  • Get data from multiple Sources to fill a datawarehouse
  • Create an unique dataset from fragmented data incoming from multiple softwares

How does it works

As always, it starts with a simple query.

If we place ourselves in the context of a single database, if we want a SELECT statement containing data from different tables, we’ll rely on Joins.

That’s pretty much how the cross-query feature is handled.

In this example, I’ll show you a cross-query between 3 unlinked databases but you can mix with files, webservices… Any type of connection will work !

  1. Create a new query. This one is associated with a MySQL DB. Right-click to make the contextual menu appear :
The Query contextual menu includes a cross-query script assistant.

2. In the cross-query assistant menu, choose a new connection :

The cross-query menu

3. The script will be added to your original Query :

[–[12]] is the script that enables a cross-query with connection ID #12

4. You can now complete your initial query by writing a new one following the cross-query script. This one is related to a Postgres database : the Posgres syntax applies. Right-click on your query to open the contextual menu :

The contextual menu now includes a “cross-query” section.

5. The cross-query is not something magical : the link between the Sources if performed by using identical field names. It means that you must have common fields in both queries.

5.1 If you don’t have identical field names, you can use aliases :

First query : SELECT id_sample, li_sample FROM mytable

Second query : SELECT mycolumn as id_sample, dt_date FROM mytable

5.2 If you want to force the link, the script can be modified like this :

You can force the link field using this syntax.
You can add more fields : [-id_sample,id_another_field-[12]]

6. You can check how the cross-query behavior in the contextual menu :

The cross-query menu. There is a lot of information to help you writing a correct Query.

7. You can also check the link. Fuzible will try both queries separately and check the syntax :

8. Now, I’ll add a new cross-query with another MariaDB database :

The new cross-query, using a LEFT JOIN link.
An alias is used to match the link (FirstColumn -> id_sample)

9. Time to run the Query by pressing “F5′ :

The “Show Source data” menu. Data from 3 sources has been successfully retrieved, according to the link-type that was scripted.

The conditional Cross-Query (advanced feat.)

Use Cases :

  • Integrity check between two databases

In case you would like to only retrieve only the differences between 2 databases (or any other sources), you can write some special condition to the cross-query script. It’s exactly what would a filtered outer join would do.

A filter was added inside the cross-query script

Without the filter, we would retrieve all the matching rows between both Sources :

Result set with no filter

With the filter, we only retrieve the matching rows with comparison filter applied

Result set with the filter