I'm trying to create a view that combines these two materialized views with the following rules.
Returns only rows that exist in all_products, though they exist in visible_products, but if there is no result in visible_products, I want all the rows of all_products
I am unable to figure it out with links or union. Can this be solved with a case description? If both tables have rows, the inner join gives me the right result, and if visible_products do not have matching rows, a left join works, but I need a join or some other solution that works in both cases.
Both all_products and visible_products are materialized views where I can modify / add columns as needed.
all_products id, market_id 1, 100 1, 101 1, 102 visible_products id, market_id 1, 100 result product_view 1,100
all_products id, market_id 2, 103 2, 104 2, 105 visible_products id, market_id result product_view 2, 103 2, 104 2, 105
Any help is greatly appreciated