# Join tables

> A **Join table** is a combined table based on a relationship between two tables, for example `Invoice_number` or `Event_ID`. Join tables are defined by a join expression and can be recognized by the **Join table** icon.

## Introduction

A **Join table** is a combined table based on a relationship between two tables, for example `Invoice_number` or `Event_ID`. Join tables are defined by a join expression and can be recognized by the **Join table** icon.

### Join types

Below is an overview of the different join types.

#### Inner join

Select all records from Table A and Table B, where the join condition is met.

![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-55870-f1a1adf0-62284cc0.webp)

**Syntax:** `TableA inner join TableB on TableA.ID=ratings.TableB.ID`

### Left Join

Select all records from Table A, along with records from Table B for which the join condition is met (if at all).

![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-53988-a86e1886-ddb321ca.webp)

**Syntax:** `TableA inner join TableB on TableA.ID=ratings.TableB.ID`

### Right Join

Select all records from Table B, along with records from Table A for which the join condition is met.

![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54580-67f0fa55-3307bc9b.webp)

**Syntax:** `TableA right join TableB on TableA.ID=ratings.TableB.ID`

### Full Join

Select all records from Table A and Table B, regardless of whether the join condition is met or not.

![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-57317-82c26dfa-5e1cbde1.webp)

**Syntax:** `TableA full join TableB on TableA.ID=ratings.TableB.ID`

### Union

As in SQL, different clauses are available

* **Order by**: The resulting table is ordered by the given attribute.
* **Where**: The resulting table is filtered by the given expression.
* **Aliasing**: By typing a string after the table name, it will be aliased to the string provided in the rest of the expression.

As in SQL, different clauses are available

* **Order by**: The resulting table is ordered by the given attribute.
* **Where**: The resulting table is filtered by the given expression.
* **Aliasing**: By typing a string after the table name, it will be aliased to the string provided in the rest of the expression.

## Creating a Join table

Follow these steps to create a join table.

| Step | Action |
| --- | --- |
| 1 | Open your app in your development environment and go to the **Data** tab. |
| 2 | Right click in the left panel and select **New table - Join table…**. |

A new table has been added to your list of tables in the **Data** tab. The join table does not contain any table items as a join expression must be defined first.

Follow these steps to define a join expression.

| Step | Action |
| --- | --- |
| 1 | Right click on the join table and select **Edit…**. |
| 2 | Enter a join expression. |
| 3 | Click on **OK**. |

See illustration below for an example of a `leftjoin` between a table called `Invoices` and another table called `Events`. The result panel shows the record count of both tables and the record count resulting out of the join.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54608-f6dcf075-491d2530.webp)

After finalizing the join expression, the resulting items which are available in the table item list, are displayed in in the join table. The join table has two groups of items, which are the respective tables which are used for the join. See illustration below.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58254-cd0262e8-fd659b5f.webp)

## Creating attribute expressions

In a join table you can create expressions that use datasource attributes from both the original.

In the table item list, these expression are placed outside the original table groups. This means that the scope of the expression is on the join table and not on the individual tables. These groups help you as a developer to see in which table items are originally defined. See illustration below.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54156-1f062d64-845bccf8.webp)

:::note
If you double click on an expression, the **Edit Expression Attribute** dialog opens. On the right of the general section of this dialog, **Table** also displays where the expression attribute was originally defined.
:::

## Availability of table items

To control in which tables a table item will be available, it is possible to set the following availability options.

* **Public**: The item is available in the current table, and in join tables using the current table.
* **Private**: The item is available in the current table, but not in join tables using the current table.
* **Hidden**: The item is not available at all.

See illustration below.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-53612-fadc6aad-13edeb11.webp)

**Public** attributes are shown in **bold**, *private* attributes are shown in *italic* and hidden attributes are striked-through. See illustration below.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-53363-e5ad1892-3d1a6e5f.webp)

The availability of an attribute or control can be set per table. For example, if in the original table the datasource attribute is **Public**, in the join table the availability can be **Private** or **Hidden**.

:::note
If an item is not available in a certain table, then it cannot be used, for example, in expressions or charts.
:::

### Setting availability to private

Follow these steps to set the availability of a datasource attribute to **Private**.

| Step | Action |
| --- | --- |
| 1 | Go to the **Data** tab. |
| 2 | Select the table that contains the attribute you want to set to **Private**. |
| 3 | Right click on attribute in the table item list and select **Availability - Private**. |

The attribute is now displayed *Italic* and is greyed out, which means it is **Private**. See illustration below for an example.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-54548-285d6df5-c99fd6bb.webp)

### Setting availability to hidden

Follow these steps to set the availability of a datasource attribute to **Hidden**.

| Step | Action |
| --- | --- |
| 1 | Go to the **Data** tab. |
| 2 | Select the table that contains the attribute you want to set to **Hidden**. |
| 3 | Right click on attribute in the table item list and select **Availability - Hidden**. |

The attribute is not visible anymore in the table item list.

:::note
Hiding a table item ensures the item cannot be used anymore. If the item was still referenced before hiding it, you will create so called “ghosts”. See [Maintain Application Integrity](https://docs.uipath.com/process-mining/standalone/2021.10/user-guide/maintain-application-integrity#maintain-application-integrity).
:::

Click on the menu in the table item list to enable the option **Show hidden**. This enables you to see table items which are set to **Hidden** in a table, and to change their availability. See illustration below.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-53720-3a6396f4-e10d2421.webp)

:::note
The **Show hidden** setting is remembered per user and is not set per application.
:::

## Filter table records

Join tables can also filter data records by using the `where` clause in the join expression. The where clause works the same as in SQL.

For example: `Invoices leftjoin Events on Invoices.Invoice_number = Events.Invoice_number where Country = 'UK'`.

Immediately after applying the change to the join table, the **Table Item Mappings** dialog shows up to map the new table items. This is necessary as the new set of table items might be different than original one. See illustration below.

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58249-7051fd99-38ae07ca.webp)

Under **Branches**, the used tables are shown, under **Table Items**, the items from these tables.

### Map table items

Follow these steps to inspect the mappings of the table items.

| Step | Action |
| --- | --- |
| 1 | Click on the **Expand** icon next to each option in the **Table Item Mappings** panel. |
| 2 | Verify the mapping is correct and click on **OK**. |

It is possible that all items were able to be mapped automatically. Depending on the change made to the join expression, some table items will not be mapped yet, and a decision must be made how to proceed. See illustration below for an example

  ![docs image](https://dev-assets.cms.uipath.com/assets/images/process-mining/process-mining-docs-image-58817-fc8a211e-c7c2452a.webp)

Not all mappings were able to be automatically detected. In the **Warning** section at the top, the table items that do not have a correct mapping are listed.

### Fixing incorrect mappings

Follow these steps to fix incorrect mappings.

1. Map the tables in the **Branches** section as good as possible.
2. Map multiple table items at once by mapping the paths marked with **Incorrect** and shown in red in the **Table Items** section.
3. Expand the paths which still have problems and try to map items one by one.
4. For items that cannot be mapped, choose **Ghost** and resolve those manually afterward

When the number of table items is small, you can start with step 3 as there are only a few mappings to correct anyway.

## Related information

See [Maintain Application Integrity](https://docs.uipath.com/process-mining/standalone/2021.10/user-guide/maintain-application-integrity#maintain-application-integrity).