If you’re working in Wdata and need to build out queries that bring together data from multiple tables, you may be wondering how to connect those tables together to give you data from both. If so, this is the community post for you!

Let’s say you have one table that stores your fact data and a separate table that stores your mapping data. You want to bring these data sets together to make your fact data more descriptive to end users. To do that, you will first need to create a query using both tables, then establish the relationships between the tables. Query relationships, or joins, are used to show how various sets of data are connected to each other, based on a shared field.

For example, we have our two tables: a fact table and a dimension table. Our fact table stores transactional data showing what each department has spent across the year. Our dimension table stores mapping data that describes the departments across our organization. Our fact data uses department codes to ensure each transaction is applied to the correct department. The department code doesn’t mean much to the people consuming the data, so we want to show the department name instead. We can use fields from both tables in our query results by creating relationships. First, we need to determine which field is shared across both tables.

After getting a good understanding of our data, we see that the Department Code field matches across both tables. We will use that field to create the relationship. Next, we need to decide which type of join is best for our use case. 

Left Join - brings in all the transactional data from the table on the left and maps it to the data that matches in the table on the right. All data from the table on the left is given to us in our query results even if there is not a match found. For example, department code 1005 exists in our transactions but does not have a match in our mapping data.

Right Join - brings in all the department mapping data from the table on the right and maps it to the data that matches in the table on the left. All data from the table on the right is given to us in our query results even if there is not a match found. For example, the Research department exists in our department mapping but does not have any transactional data.

Inner Join - only includes records that are found across both sets of data, excluding all records where matches are not found. For example: our transactional data for department code 1005 and our mapping data for the Research department were both left out of our results because no matches were found.

Full Join - brings in all data across both tables, matching data together when matches are found. For example, most of our records found matches, but where matches were not found, we have null data.

After reviewing all of the options, we have determined that we want to include all transactional data, even if a matching department was not found, so we will use a left join. 

Which join works best for your use case? Let us know in the comments!