1

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

1
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
      )
  • Thanks! It looks like the second select is not working correctly for ex b. I will need to set up a better test tomorrow though as its late here and it worked on a small test. I think it could be when there is more id's in all_products it fails somehow. But thanks I think its close – Jonas Nov 10 '19 at 21:45
1

Based on the example @luuk wrote I got it working by changing it slightly to the below code (work in progress). It is however too slow as the two tables are really big (1m+rows). Not sure if it is easier to have two separate queries and then programatically figure out which to pick or if I can't rethink this.

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   
    ap.id, 
    ap.market_id
    FROM all_products ap
    WHERE ap.id NOT IN
        (SELECT vp.id 
        FROM visible_products vp)
  • I think it was late yesterday when I typed this.... If I look at the first part (before the UNION ALL, it's can be simplified because it currently says SELECT records WHERE there are records in the SELECT, the compete WHERE-clause can be deleted. – Luuk Nov 11 '19 at 19:33
  • About the secons part (after the UNION ALL) , maybe performance gets better when you do SELECT … FROM all_products ap LEFT JOIN visible_products vp ON vp.id=ap.id WHERE vp.id SI NULL – Luuk Nov 11 '19 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.