You can translate the question and the replies:

JOINING LEFT VIEW WITH PRIMARY KEY TO RIGHT VIEW WITH A COLUMN WITHOU PRIMARY KEY

Hello. I have a view named left_view with 5 rows: | column_1 | column_2 |column_3 | | -------- | -------- | -------- | | abc | some value | some value | | def | some value | some value | | ghi | some value |some value | | jkl | some value |some value | | mno | some value |some value | column_1 is the primary key of the above left_view. I also a view named right_view with 10 rows: | column_a | column_b | column_c | | -------- | -------- | -------- | | abc | some value | some value | | def | some value | some value | | ghi | some value |some value | | jkl | some value |some value | | mno | some value |some value | | abc | some value | some value | | def | some value | some value | | ghi | some value |some value | | jkl | some value |some value | | mno | some value |some value | The above right_view does not have a primary key. However, the column_a of right_view contains the values in column_1 of the left_view. The issue is that there are duplicate values in column_a of right view. I want to join the two views so I get the following joined_view with 5 rows (the number of rows in left_view) and all the columns of both the left_view and the right_view: | column_1 | column_2 | column_3 | column_a | column_b | column_c | | -------- | -------- | -------- | | abc | some value | some value | some value | some value | some value | | def | some value | some value | some value | some value | some value | | ghi | some value |some value | some value | some value | some value | | jkl | some value |some value | some value | some value | some value | | mno | some value |some value | some value | some value | some value | I performed a left outer join of the left_view and the right_view by using left_view's column_1 and right_view's column_a in the join condition (left_view.column_1 = right_view.column_a). However, I am getting a view with 10 rows (the number of rows in the right_view), instead of a view with the expected 5 rows that left_view has. How can I get the desired join_view with only 5 rows corresponding to those of the left_view and all the columns of both the left_view and the right_view, please? Any help would be greatly appreciated.
join Duplicates Distinct primary keys left join
user
14-09-2023 17:16:42 -0400
code

1 Answer

Hi, I would first remove the duplicate values from the right_view. Executing the join after would return the intended result of 5 rows, instead of 10. To accomplish this, I would create a selection view on top of ‘right_view’, and select the ‘Distinct clause’ box in the output tab. This will create a view that only returns unique values of ‘column_a’, meaning our new selection would only have 5 rows. This logic is also explained in [another Denodo Community Question](https://community.denodo.com/answers/question/details?questionId=9060g0000000A0dAAE&title=How+to+access+unique%2Fdistinct+values+from+the+column+in+a+Base+view+in+RESTful+web+service%3F) that needs to have DISTINCT applied to a column. After creating the selection view, joining ‘left_view’ and ‘selection_right_view’ will then return the intended result of 5 rows, since we removed duplicates above. Hope this helps!
Denodo Team
15-09-2023 12:39:13 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here