Postgresql – Question on conditional join or other way to create view based on two tables in Postgresql

postgresqlpostgresql-11

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.

Example a

all_products
id, market_id
1, 100
1, 101
1, 102

visible_products
id, market_id
1, 100

result product_view
1,100

Example b

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

Jonas

Best Answer

SELECT a.id, a.market_id
FROM all_products a
INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
WHERE EXISTS (SELECT 1
       FROM all_products a
       INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
      )

UNION ALL

SELECT a.id, a.market_id
FROM all_products a
WHERE NOT EXISTS (SELECT 1
       FROM all_products a
       INNER JOIN visible_products v ON v.id=a.id AND v.market_id=a.market_ID
      )
Related Question