I am trying to create a view that combines these two materialized views with following rules.
- if all_products.id and there is matching id (market_id not relevant) in visible_products then only return visible_products rows
- if all_products.id and there is no matching id in visible_products (market_id is not relevant in match) then return rows from all_products
I am not able to figure it out with joins or union. Can this be solved with a case statement? If both tables have rows then inner join will give me the correct result and if visible_products have no matching rows a left join will work but I need a join or other solution that will work with both cases.
Both all_products and visible_products are materialzied views that I can change / add columns to if 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