# Merging Queries

**URL:** <https://community.omni.co/t/merging-queries/151>\
**Category:** Patterns\
**Created:** [September 16, 2024, 7:20pm UTC](https://community.omni.co/t/merging-queries/151 "2024-09-16T19:20:40Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![conner](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conner/32/11_2.png) [@conner](https://community.omni.co/u/conner)\
**Post date:** [September 16, 2024, 7:20pm UTC](https://community.omni.co/t/merging-queries/151/1 "2024-09-16T19:20:40Z")

</div>

**TL;DR: How to combine queries across tables on a foreign key**

Sometimes the join between two tables is easy, like joining `orders` to `products` on `product_id`.

But sometimes it’s more complex. For example, let’s say you want to make a daily rollup table with the count of orders and distinct products per day. You would need to “merge” two aggregate queries — one for `order` per day, and one for `products` per day — by joining them on a date field, which would let you plot both metrics in a single chart.

 ![Merging total_orders and total_products into one table](https://canada1.discourse-cdn.com/flex004/uploads/omni/original/1X/e8b1a9e7e93f2df0f4239d1f46bef0f19a74344a.png)

“Merging queries” like this is a common analytical pattern. There are a few ways to do it in Omni:

1. Xlookup (easier)
2. Saved views (if you prefer to formally model)

## 1. Xlookup

[Xlookup](https://docs.omni.co/docs/querying-and-sql/table-calculations#xlookup) in Omni makes this pattern very easy to do. Simply create your two queries you’d like to merge, and then use Xlookup to bring data from one query to the other.

Here’s how this looks in practice:

\*\*Note:\*\*This works very well when you know the join is one-to-one; for example, a daily rollup table → another daily rollup table = one date per table. But if your join is one-to-many, Xlookup will only pull the first result it encounters, so the results may not be what you expect. In that case, use the Saved Views method below.

## 2. Saved Views

To “merge” using Omni’s modeling structure, we can create separate [Saved Views](https://docs.exploreomni.com/querying-and-sql/saved-views) and join them on a common key. For example:

1. Aggregate an `orders` table to orders per day and a `products` table to products per day
2. Join the tables together in the UI or in the `relationships` file
3. Query from the tables

Here’s how this looks in practice: