What if your chart needs data from multiple data sources or from tables with no foreign key relations? You can add another Query to your Pipeline, which lets you pull data from a different data source or unrelated table. Once you’ve built and run your second query, you’ll need to merge the Queries together with a Join, blending them into a single result set for your chart. Chartio makes it fast and easy to merge the result sets of disparate data sources, allowing for powerful post-processing and calculations.
Add another Query
After executing your initial Query, you can add a new Query to your Pipeline by clicking Add Query above the Result Table. Depending on the type of join you have in mind, you may want to consider having a related (or common) column to join your Queries on. A common column refers to a column in each result set containing similar values. For example, if two queries share a date column, merging the queries will combine records with matching dates, resulting in a more insightful output.
Transform your Query
After you’ve built and executed (i.e., clicked Run Query) your new Query, you can transform its result set with other Actions before merging with another result set.
Merge two result sets
When you’re ready to merge, select the Action (Query or otherwise) in your Pipeline to merge with your second Query’s result set. You have the flexibility of merging with the inital Query’s results or merging with the result set of another Action in the Pipeline. In other words, you may want to merge your new Query with the transformed result set of another Query.
If you hover over the Merge Queries button, a placeholder image appears in the Pipeline, showing you where your new Query and all the pre-merge transformation Actions you applied to it will be added. Generally, they’re nested beneath the Action you merge result sets with.
Click Merge Queries above the Result Table to merge the result sets.
Choose your join type
As mentioned above, you can change the join type of your merge. When you merge, the left table’s data is joined with the right table’s data. Think of the top result set as the left table and the nested result set (i.e., the new Query’s result set) as the right table. This may help you choose your join type.
Each join type may have other fields you can change to customize your merge. For more details on these fields, check out our Join Action documentation.
If you’re not sure which join to use, here are brief descriptions and illustrations for each available join type:
|Combines the columns from all result sets on one or more common columns when possible, and includes all data from both result sets.|
|INNER JOIN||Combines the columns on a common column (the first N columns) when possible, and only includes data for the columns that share the same values in the common N column(s).|
|LEFT JOIN||Combines the columns on a common column (the first N columns) when possible, returning all rows from the first result set with the matching rows in the second result set. The result is NULL in the second result set when there is no match.|
|UNION||A Union merge will stack the result sets on top of each other without grouping or combining the data. Unions can be used to generate lists of data to be printed or viewed in table format. To remove duplicate rows, check the Distinct checkbox.|
|CROSS JOIN||The result of the Cross Join will be a table with all possible combinations of your result sets together. This can result in enormous tables and should be used with caution. Cross Joins will likely only be used when your result sets are returning single values.|
If you still need some help understanding join types, check out this awesome article from our Data School that gives visual explanations of SQL join types.