How to perform a one-to-many join in Upsolver – Upsolver

In most situations where a lookup is used, you're enriching data based on a key, this is a one-to-one relationship. For instance you may have a data source with orders and a data source with shipping information joined via a foreign key.

In Upsolver, this is easy enough to represent through a LEFT JOIN:

SELECT data.id
       data.customer_name
       shipping.tracking_number
FROM "orders" LEFT JOIN
    (SELECT data.order_id AS order_id
            LAST(data.tracking_number) as tracking_number
     FROM "shipping"
     GROUP BY data.order_id) as shipping ON shipping.order_id = data.id

However, there may be situations that you need to associate one-to-many. For instance, what if we had an orders data source with an items data source. An order can contain many items.

If every order had AT LEAST one item, we can reverse the JOIN and join orders to items using a LEFT JOIN

SELECT orders.id,
       orders.customer_name,
       orders.item_id,
       data.item_name
FROM "items"
LEFT JOIN (
           SELECT data.id as id,
                  LAST(data.customer_name) as customer_name
           FROM "orders"
           GROUP BY data.id
           ) as orders 
ON data.order_id = orders.id

Now, if it is possible to have an entry in the orders data source, without an entry in the items data source, this method won't work, as the lookup only occurs for each item.

To perform that logic, you will need to do two joins, one to a collection of items, and the second to the item name. Logically you can envision this as three tables.

In SQL this would be represented as two LEFT JOINs using the COLLECT_SET() function.

SELECT data.id as order_id,
       data.customer_name,
       items.item_id,
       items.item_name
FROM "orders"
LEFT JOIN (
      SELECT order_id,
             COLLECT_SET(data.id) as item_ids
      FROM "items"
      GROUP BY order_id
      ) AS order_items on data.id = order_items.order_id
LEFT JOIN (
      SELECT data.id as item_id,
             LAST(data.item_name) as item_name
      FROM "items"
      GROUP BY data.id
) as items ON order_items.item_ids[] = items.item_id

Related articles