# Joining to an aliased view **URL:** \ **Category:** Modeling\ **Tags:** alias, topic-scoped-views\ **Created:** [August 13, 2024, 8:31pm UTC](https://community.omni.co/t/joining-to-an-aliased-view/139 "2024-08-13T20:31:01Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![conor](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.omni.co/conor/32/45_2.png) [@conor](https://community.omni.co/u/conor)\ **Post date:** [August 13, 2024, 8:31pm UTC](https://community.omni.co/t/joining-to-an-aliased-view/139/1 "2024-08-13T20:31:01Z")
Sometimes views need to be aliased or duplicated and can be automatically created using joins. In a previous article [How to join the same table twice](https://community.omni.co/t/how-to-join-the-same-table-twice/46) we used aliasing to join the same tables in different ways. In that case, we joined the same two tables `order_items` and `users` with differing join conditions and refer to the separate results using an alias, for one join `buyers` and in another it was `sellers`. Often, the next step will be to create a relationship to this alias. In this article we’ll show you how to do that. ## Worked example with steps In this example we have 3 tables `order_items`, `users` and `inventory_items`. First we will create a table called `first_orders` for every user’s first order. Then we’ll join that result to `inventory_items` to get product information on the first order. (We’ll assume that this dataset only allows one order per timestamp, so `users` and `order_items` have a 1:1 relationship for the first order.) 1. Create the first alias relationship ([docs](https://docs.omni.co/docs/modeling/relationships)) using the table names in `join_from_view`, `join_to_view` and create the alias name in `join_to_view_as:`. In the join condition `on_sql` use the alias name instead of the table name. For this example it will look like the following: ```yml #relationships file - join_from_view: users join_to_view: order_items join_to_view_as: first_order join_type: inner on_sql: ${first_order.user_id} = ${users.id} and ${first_order.created_at} = ${users.created_at} relationship_type: one_to_one ``` 1. Create a topic (we’ll name it `demo_topic`) and add `users` as a base table and join the aliased table to it. We’ll add some labels in too using `base_view_label` and `label` (refer to [topic parameter docs](https://docs.omni.co/docs/modeling/topics#topic-parameters)). ```yml # model file topics: demo_topic: base_view: users base_view_label: Users joins: first_order: {} label: Demo Topic ``` 1. Create the join to this alias. In `relationships`, use table names for `join_from_view`, `join_to_view` and create the alias name in `join_to_view_as:`. Again, in the join condition `on_sql` use the alias name instead of the table name. This example will call the result `first_inventory_items`. ```yml ## relationships file - join_from_view: order_items join_to_view: inventory_items join_to_view_as: first_inventory_items join_type: always_left on_sql: ${order_items.inventory_item_id} = ${first_inventory_items.id} relationship_type: one_to_one ``` 1. Update the model file to include the relationship to the new joined in table ```auto ## updated model file topics: demo_topic: base_view: users base_view_label: Users joins: first_order: first_inventory_items: {} label: Demo Topic ``` ## Further things to consider If there are measures which we only want to use in the context of this relationship, it’s worth considering where to define these. If we define them in the `users.view` file, they will be included automatically in all topics that use that view, which might not be the desired behaviour. One way to solve this, would be to exclude this new metric (e.g. `users.number_of_items_in_first_order`) from other topic using the fields parameter like so: ```auto fields: [all_views.*, -users.number_of_items_in_first_order] ``` ### Topic Scoped Views If you don’t want to add these measures/dimensions to the view file and explicitly remove them from certain topics, you can use [topic scoped views](https://www.youtube.com/watch?v=CTYR30-tKps). With these we can add dimensions and metrics to a view just within a topic, so it doesn’t exist everywhere and we don’t have to explicitly remove it from other topics. Here’s how we would define the topic-scoped view by extending the original view just for the context of this topic. ```auto # model file topics: demo_topic: base_view: users base_view_label: Users joins: first_order: first_inventory_items: {} label: Demo Topic # define a topic-scoped view to extend the definition of users just for this topic. views: users: measures: number_of_items_in_first_order: sql: ${count}*10000 ``` ## Conclusion To wrap up, in this article we covered how we’d join to an aliased table, to give us the added flexibility in data modeling to handle complex relationships. When implementing the relationships, remember the parameters `join_from_view` and `join_to_view` should refer to the view name, the `on_sql` parameter should use the alias name defined in `join_to_view_as` parameter. In the model file where the topic is defined, use the alias names. Finally we covered the pros and cons of where to define your dimensions and measures which may only be relevant for the relationships created with the aliases. If you want these dimensions and measures to appear in all topics, you can define them in the view file, but you’ll need to explicitly remove them from topics where they’re not needed. To add them to just one topic, you can use **topic-scoped views**.