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 !
- Create a new query. This one is associated with a MySQL DB. Right-click to make the contextual menu appear :
2. In the cross-query assistant menu, choose a new connection :
3. The script will be added to your original Query :
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 :
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 add more fields : [-id_sample,id_another_field-[12]]
6. You can check how the cross-query behavior in the contextual menu :
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 :
An alias is used to match the link (FirstColumn -> id_sample)
9. Time to run the Query by pressing “F5′ :
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.
Without the filter, we would retrieve all the matching rows between both Sources :
With the filter, we only retrieve the matching rows with comparison filter applied
English 
French