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
Comments
0 comments
Please sign in to leave a comment.