> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-cube-duplicate-cube-joins-error.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Joins
> Joins define relationships between cubes, allowing Cube to automatically generate multi-table SQL queries when views combine data from multiple cubes.
Joins define how cubes connect to each other. When a [view][ref-views]
includes members from multiple cubes, Cube uses these relationships to
automatically generate SQL `JOIN` clauses — so end-users can explore data
across tables without writing SQL.
See the [joins reference][ref-schema-ref-joins-relationship] for the full
list of parameters and configuration options.
## Relationship types
Cube supports three relationship types: `one_to_one`, `one_to_many`, and
`many_to_one`. The relationship type determines which table becomes the left
side of the `LEFT JOIN` in the generated SQL.
Consider two cubes, `orders` and `customers`. An order belongs to one
customer, but a customer can have many orders:
```yaml title="YAML" theme={null}
cubes:
- name: orders
sql_table: orders
joins:
- name: customers
relationship: many_to_one
sql: "{CUBE}.customer_id = {customers.id}"
dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: status
sql: status
type: string
measures:
- name: count
type: count
- name: customers
sql_table: customers
dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: company
sql: company
type: string
```
```javascript title="JavaScript" theme={null}
cube(`orders`, {
sql_table: `orders`,
joins: {
customers: {
relationship: `many_to_one`,
sql: `${CUBE}.customer_id = ${customers.id}`
}
},
dimensions: {
id: { sql: `id`, type: `number`, primary_key: true },
status: { sql: `status`, type: `string` }
},
measures: {
count: { type: `count` }
}
})
cube(`customers`, {
sql_table: `customers`,
dimensions: {
id: { sql: `id`, type: `number`, primary_key: true },
company: { sql: `company`, type: `string` }
}
})
```
The `many_to_one` join on `orders` means: many orders belong to one customer.
When a view includes members from both cubes, Cube generates SQL with `orders`
on the left and `customers` on the right:
```sql theme={null}
SELECT
"orders".status,
"customers".company,
COUNT("orders".id)
FROM orders AS "orders"
LEFT JOIN customers AS "customers"
ON "orders".customer_id = "customers".id
GROUP BY 1, 2
```
Because `orders` is on the left side of the `LEFT JOIN`, all orders are
preserved — including guest checkouts with no matching customer.
As a rule of thumb, define joins on the **fact table** (e.g., `orders`)
pointing toward the **dimension table** (e.g., `customers`) using
`many_to_one`. This ensures the fact table is always the base of the query,
preserving all its rows.
### Many-to-many relationships
A many-to-many relationship requires an associative (junction) table. For
example, `posts` and `topics` are connected through a `post_topics` table:
Model this with an associative cube, chaining the joins so they flow in one
direction (`posts → post_topics → topics`):
```yaml title="YAML" theme={null}
cubes:
- name: posts
sql_table: posts
joins:
- name: post_topics
relationship: one_to_many
sql: "{CUBE}.id = {post_topics.post_id}"
- name: post_topics
sql_table: post_topics
joins:
- name: topics
relationship: many_to_one
sql: "{CUBE}.topic_id = {topics.id}"
dimensions:
- name: id
sql: "CONCAT({CUBE}.post_id, {CUBE}.topic_id)"
type: string
primary_key: true
- name: topics
sql_table: topics
dimensions:
- name: id
sql: id
type: string
primary_key: true
- name: name
sql: name
type: string
```
```javascript title="JavaScript" theme={null}
cube(`posts`, {
sql_table: `posts`,
joins: {
post_topics: {
relationship: `one_to_many`,
sql: `${CUBE}.id = ${post_topics.post_id}`
}
}
})
cube(`post_topics`, {
sql_table: `post_topics`,
joins: {
topics: {
relationship: `many_to_one`,
sql: `${CUBE}.topic_id = ${topics.id}`
}
},
dimensions: {
id: {
sql: `CONCAT(${CUBE}.post_id, ${CUBE}.topic_id)`,
type: `string`,
primary_key: true
}
}
})
cube(`topics`, {
sql_table: `topics`,
dimensions: {
id: { sql: `id`, type: `string`, primary_key: true },
name: { sql: `name`, type: `string` }
}
})
```
A view can then expose this through the `join_path`:
```yaml theme={null}
views:
- name: posts_with_topics
cubes:
- join_path: posts
includes:
- title
- count
- join_path: posts.post_topics.topics
prefix: true
includes:
- name
```
## Direction of joins
**All joins are directed.** They flow from the source cube (where the join
is defined) to the target cube (the one referenced). Cube places the source
cube on the left side of the `LEFT JOIN` and the target on the right.
This matters because the left table preserves all its rows, while the right
table contributes matching rows or `NULL`. The direction you choose affects
which records appear in the result set.
For example, if `orders` defines a `many_to_one` join to `customers`:
* `orders` is the base → all orders are preserved, even guest checkouts
* `customers` without orders won't appear
If instead `customers` defined a `one_to_many` join to `orders`:
* `customers` is the base → all customers are preserved, even those without orders
* Guest checkout orders (with no matching customer) won't appear
### Using views to control direction
Views let you control which join path is followed via the
[`join_path`][ref-view-join-path] parameter. This is the recommended way to
handle cases where you need different join directions for different use cases:
```yaml title="YAML" theme={null}
cubes:
- name: orders
sql_table: orders
joins:
- name: customers
sql: "{CUBE}.customer_id = {customers.id}"
relationship: many_to_one
measures:
- name: count
type: count
- name: total_revenue
sql: revenue
type: sum
dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: customers
sql_table: customers
joins:
- name: orders
sql: "{CUBE}.id = {orders.customer_id}"
relationship: one_to_many
measures:
- name: count
type: count
dimensions:
- name: id
sql: id
type: number
primary_key: true
- name: name
sql: name
type: string
```
```javascript title="JavaScript" theme={null}
cube(`orders`, {
sql_table: `orders`,
joins: {
customers: {
sql: `${CUBE}.customer_id = ${customers.id}`,
relationship: `many_to_one`
}
},
measures: {
count: { type: `count` },
total_revenue: { sql: `revenue`, type: `sum` }
},
dimensions: {
id: { sql: `id`, type: `number`, primary_key: true }
}
})
cube(`customers`, {
sql_table: `customers`,
joins: {
orders: {
sql: `${CUBE}.id = ${orders.customer_id}`,
relationship: `one_to_many`
}
},
measures: {
count: { type: `count` }
},
dimensions: {
id: { sql: `id`, type: `number`, primary_key: true },
name: { sql: `name`, type: `string` }
}
})
```
Now you can create two views for two different analytical needs:
```yaml title="YAML" theme={null}
views:
- name: revenue_per_customer
description: All orders with customer details. Includes guest checkouts.
cubes:
- join_path: orders
includes:
- count
- total_revenue
- join_path: orders.customers
includes:
- name
- name: customer_activity
description: All customers with their order activity. Includes customers without orders.
cubes:
- join_path: customers
includes:
- name
- count
- join_path: customers.orders
prefix: true
includes:
- count
- total_revenue
```
```javascript title="JavaScript" theme={null}
view(`revenue_per_customer`, {
description: `All orders with customer details. Includes guest checkouts.`,
cubes: [
{
join_path: orders,
includes: [`count`, `total_revenue`]
},
{
join_path: orders.customers,
includes: [`name`]
}
]
})
view(`customer_activity`, {
description: `All customers with their order activity. Includes customers without orders.`,
cubes: [
{
join_path: customers,
includes: [`name`, `count`]
},
{
join_path: customers.orders,
prefix: true,
includes: [`count`, `total_revenue`]
}
]
})
```
The `revenue_per_customer` view follows the `orders → customers` path, so all
orders are preserved. The `customer_activity` view follows
`customers → orders`, so all customers are preserved.
## Diamond subgraphs
A *diamond subgraph* occurs when there's more than one join path between two
cubes — for example, `users.schools.countries` and
`users.employers.countries`. This can lead to ambiguous query generation.
Views resolve this ambiguity by specifying the exact `join_path` for each
included cube. For example, if cube `a` joins to both `b` and `c`, and both
`b` and `c` join to `d`, a view can specify which path to follow:
```yaml theme={null}
views:
- name: a_with_d_via_b
cubes:
- join_path: a
includes: "*"
- join_path: a.b.d
prefix: true
includes:
- value
- name: a_with_d_via_c
cubes:
- join_path: a
includes: "*"
- join_path: a.c.d
prefix: true
includes:
- value
```
Each view follows a specific, unambiguous path through the data graph.
## Join paths in calculated members
When referencing a member of another cube in a [calculated member][ref-calculated-members],
you can use a join path to specify the exact route. This uses dot-separated
cube names:
```yaml title="YAML" theme={null}
cubes:
- name: orders
# ...
dimensions:
- name: customer_country
sql: "{customers.country}"
type: string
- name: shipping_country
sql: "{shipping_addresses.country}"
type: string
```
```javascript title="JavaScript" theme={null}
cube(`orders`, {
// ...
dimensions: {
customer_country: {
sql: `${customers.country}`,
type: `string`
},
shipping_country: {
sql: `${shipping_addresses.country}`,
type: `string`
}
}
})
```
## Troubleshooting
### `Can't find join path`
The error `Can't find join path to join 'cube_a', 'cube_b'` means the cubes
included in a view or query can't be connected through the defined joins.
Check that:
* Joins are defined with the correct [direction](#direction-of-joins)
* There is a continuous path from the source cube to the target cube
* You're using the [`join_path`][ref-view-join-path] parameter in views to
specify the exact path
### `Primary key is required when join is defined`
Cube uses primary keys to avoid fanouts — when rows get duplicated during
joins and aggregates are over-counted. Define a [primary key][ref-primary-key]
dimension in every cube that participates in joins.
If your data doesn't have a natural primary key, create a composite one:
```yaml theme={null}
cubes:
- name: events
# ...
dimensions:
- name: composite_key
sql: CONCAT(column_a, '-', column_b, '-', column_c)
type: string
primary_key: true
```
### `Only one join per pair of cubes is supported`
A cube can define at most one join to any other cube. Two joins to the same
cube would leave the join path ambiguous, so the data model fails to compile.
To join the same table through two different keys, use
[`extends`][ref-extends] to create a second cube over it and join that:
```yaml theme={null}
cubes:
- name: users
sql_table: users
- name: managers
extends: users
- name: orders
sql_table: orders
joins:
- name: users
sql: "{CUBE}.user_id = {users}.id"
relationship: many_to_one
- name: managers
sql: "{CUBE}.manager_id = {managers}.id"
relationship: many_to_one
```
A cube that `extends` another may redefine a join it inherits; that replaces
the inherited one rather than adding a second join.
[ref-schema-ref-joins-relationship]: /reference/data-modeling/joins
[ref-views]: /docs/data-modeling/views
[ref-view-join-path]: /reference/data-modeling/view#join_path
[ref-calculated-members]: /docs/data-modeling/measures#calculated-measures
[ref-primary-key]: /reference/data-modeling/dimensions#primary_key
[ref-visual-model]: /docs/data-modeling/visual-modeler
[ref-extends]: /reference/data-modeling/cube#extends