When you have two data tables that both share a common column, the Join Tool can be used to merge those two tables into one. For example, let's say we have a table containing Products information:
| Product Code | Price | Weight | Stock Level |
|---|---|---|---|
| ABC123 | 23.99 | 8.43 | 51 |
| AD4923 | 19.99 | 0.23 | 11 |
| D48250 | 11.99 | 1.43 | 38 |
And we have another table contain Invoice Lines:
| Invoice Line ID | Invoice Number | Product Code | Quantity | Cost |
|---|---|---|---|---|
| 00001 | 193/0001 | ABC123 | 2 | 47.98 |
| 00002 | 193/0002 | AD4923 | 1 | 19.99 |
| 00003 | 193/0003 | D48250 | 1 | 11.99 |
| 00004 | 193/0004 | D48250 | 1 | 11.99 |
| 00005 | 193/0004 | AD4923 | 3 | 59.97 |
The Invoice Lines table has some useful data in it, but the only product information is the product code. If we want to filter, sort or group invoice lines by some aspect of the products on the product table then we'll need to merge these two tables together. This is what the Join Tool does.
The Join Tool connects to exactly two inputs. Once you've done this, the Join Tool's options window will look something like this:
You'll need to ensure that the Table 1 Column and Table 2 Column settings are set to the column in each input table that contains the shared values, e.g. "Product Code". Once you've done this, clicking Ok will tell the Join Tool to join both tables into one:
| Product Code | Price | Weight | Stock Level | Invoice Line ID | Invoice Number | Product Code | Quantity | Cost |
|---|---|---|---|---|---|---|---|---|
| ABC123 | 23.99 | 8.43 | 51 | 1 | 193/0001 | ABC123 | 2 | 47.98 |
| AD4923 | 19.99 | 0.23 | 11 | 2 | 193/0002 | AD4923 | 1 | 19.99 |
| D48250 | 11.99 | 1.43 | 38 | 3 | 193/0003 | D48250 | 1 | 11.99 |
| D48250 | 11.99 | 1.43 | 38 | 4 | 193/0004 | D48250 | 1 | 11.99 |
| AD4923 | 19.99 | 0.23 | 11 | 5 | 193/0004 | AD4923 | 3 | 59.97 |
Notice that some of the Product detail lines are duplicated. This is because we have more than one invoice line for the same product. The Join Tool copies the product details onto each Invoice Line row.
The Join Type setting tells the tool what to do if it encounters rows in one or other table which it does not know how to merge. It could have found a Product Code which had no invoice lines, or an invoice line for a Product Code it didn't have in the Products table. The following values for Join Type tell the tool how to behave when that sort of thing happens:
- Only matches from both: this will only include rows in the results if there is a row in both of the input tables with that value. In our example, only the Product Codes that were found in both tables will be in the results.
- All of Table 1, with matches from Table 2: This will include all the rows in table 1 and will attempt to add rows from table 2 where it sees a match. In our example, all the rows in the Product Table would be included, with Invoice Line details merged in where possible.
- All of Table 2, with matches from Table 1: This will include all the rows in table 2 and will attempt to add rows from table 1 where it sees a match. In our example, all Invoice Lines would be included, with Product details merged in where possible.
- All of both tables, matched where possible: This will include all the rows in both input tables, and will match them together where they share the same value. In our example, all of the Products and Invoice Lines tables would be in the results, but some lines might have missing Product information and some lines might have missing Invoice Line information.
- Every possible combination: This will build a table containing every possible combination of rows from the two input tables.