> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/quickstarts-1/jobs/transformation/updating-data/join-two-data-streams.md).

# Join Two Data Streams

### **Before you start**

Ensure you have created a connection to each data source. Then create a staging table for each source, and a job for each table to ingest the data into the respective table. Having completed these steps, you can then join the two data streams using a materialized view.

To join two tables in Upsolver, you must convert one of them into a materialized view that maps a set of key columns to aggregated values. When you later join your main table with the materialized view, Upsolver matches the records based on the lookup table's key column values. You can then add the values from the aggregated columns to the matching record in the main table.

### **Create a materialized view**

In this example, convert the sales table, named **sales\_info\_raw\_data**, into a materialized view to later join with the orders data in your main table.

**Here's the code to create a materialized view:**

```sql
CREATE MATERIALIZED VIEW 
   default_glue_catalog.upsolver_samples.physical_store_orders_materialized_view 
AS 
    SELECT orderid, 
       LAST(saleinfo_source) AS source,
       LAST(saleinfo_store_location_country) AS country,
       LAST(saleinfo_store_location_name) AS name,
       LAST(saleinfo_store_servicedby_employeeid) AS employeeid,
       LAST(saleinfo_store_servicedby_firstname) AS firstname,
       LAST(saleinfo_store_servicedby_lastname) AS lastname
    FROM default_glue_catalog.upsolver_samples.sales_info_raw_data
    GROUP BY orderid;
```

Let's understand what this code does.

Note the lines of code beginning with `LAST`. With these lines, you are selecting the last piece of data in each of the categories in parentheses and renaming them. For example, you select the last entry in the **saleinfo\_source** column and rename it to **country**. The query groups the aggregations by the **orderid** column.

### Create a target table for your joined data

Before you join your data, you must create a table to write the data into. This table is created in the AWS Glue Data Catalog and is available to query within Amazon Athena.

**Here's the code to create the table:**

```sql
CREATE TABLE default_glue_catalog.upsolver_samples.orders_transformed_data;
```

### Join orders with the last store employee who dealt with the order

{% hint style="info" %}
You must have data in your staging table before running this job.
{% endhint %}

The last step is to join your data. Here you use a transformation job that uses an `INSERT` statement to join the orders staging table and the sales lookup table. The join matches the key column in the lookup table to the corresponding column in the orders staging table. The job then inserts the data into the target Athena table you previously created.

**Here’s the code:**

```sql
CREATE SYNC JOB join_two_tables_orders_with_last_employee
   START_FROM = BEGINNING
   ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data 
 MAP_COLUMNS_BY_NAME
   SELECT orderid,
      l.employeeid as employeeid, 
      l.firstname as firstname, 
      l.lastname as lastname
      FROM default_glue_catalog.upsolver_samples.orders_raw_data
      LEFT JOIN physical_store_orders_lookup l
      ON l.orderid = orderid
      AS OF $event_time + INTERVAL '3' MINUTES
      WHERE l.source = 'Store'
      AND $commit_time between execution_start_time() AND execution_end_time();
```

You have successfully created a table joining physical store orders with the employee who completed the order. 


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://upsolver.gitbook.io/content/quickstarts-1/jobs/transformation/updating-data/join-two-data-streams.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.