Join

Explore how to leverage joins to retrieve data from multiple tables using a related column.

Joins are widely used to fetch or lookup data from multiple tables based on a related column. Inforbridge supports 4 types of joins:

• Inner join: Returns the matching records between 2 tables.

• Left join: Returns all records from the base(left) table and matching records from the right table.

• Right join: Returns all records from the right table and matching records from the base table.

• Full outer join: Returns all records when there is a match in either table.

We'll take a simple regional sales table to demonstrate joins.

Regional sales

We can fetch the tax and rate type for each region based on the related column - rate type ID.

Tax rate table

The resultant joined table would look like this if we were to use an inner join. Notice how the LATAM record has been dropped as it does not have a matching rate in the second table.

Region table enriched with tax rates - inner join

We can use a left outer join(or full outer join) to display the LATAM region, although it doesn't have a matching tax rate. The tax rate and rate type fields will be blank for the LATAM record when we use a left outer join.

Region data with tax rates - left join

Let's look at a use case in Infobridge where we can apply joins. Our base report contains the actuals and plan across various regions. We need to get the tax rates for each month.

Base report

The monthly tax rates are in a different query that can be pulled using the common column - Month.

Monthly tax rates

Let's select the Join Query option from the Data ribbon. Select "AC" as the base query and "Tax Rates" as the Query to join from the dropdowns. Choose "Month" as the Join Column. Select "Inner" as the Join Type.

Join query configuration

The resultant join query contains the columns from both queries. You can delete the additional "Month" dimension from the Pivot Table window.

Joined query