Aperix Aperix - 1 year ago 45
SQL Question

Need efficient SQL on joining rowcount from a materialized view with another materialized view

Database Engine I'm Using: PostgreSQL 9.5.2

What I'm trying to do:

I have the following two materialized views in my PostgreSQL:

1) state_vendors_mat_view

vendor_id sol_id states_list total_revenue
--------- ------ ----------- -------------
1234 abc CA, AZ 23000.00
5678 abc TX, FL 12000.00
9012 def AZ, TX 67000.00

2) interested_vendors_mat_view

sol_id vendor_id
------ ---------
abc 1234
def 5678

I'm trying to query the state_vendors_mat_view for a given sol_id (e.g. "abc"); however, I want to know, for each vendor_id in the result, is this vendor_id mapped to a sol_id in the interested_vendors_mat_view. If it is mapped, I want a Boolean value of true; otherwise, I want a Boolean value of false.

Furthermore, I want this query to be as efficient as possible. I don't want to run a select on the interested_vendors_mat_view for each record in the result from the state_vendors_mat_view.

Desired Results:

For example, if I want all vendors that have a sol_id = "abc", then I want the result to look like:

vendor_id is_interested sol_id states_list total_revenue
--------- ------------- ------ ----------- -------------
1234 True abc CA, AZ 23000.00
5678 False abc TX, FL 12000.00


  • The record for vendor_id=1234 has a Boolean value of True in the is_interested column because the interested_vendors_mat_view has a record that maps 1234 and "abc".

  • The record for vendor_id=5678 has a Boolean value of False in the is_interested column because the interested_vendors_mat_view does not have a record that maps 5678 and "abc".

Questions I've Looked At:

SQL I've tried:

WHEN ((SELECT count(*) FROM interested_vendors_mat_view iv
WHERE sol_id = 'abc' and iv.vendor_id = state.vendor_id) = 0)
THEN false
ELSE true
END AS is_interested,
FROM state_vendors_mat_view state WHERE sol_id = 'abc';

To try and get just the correct row counts, I've also tried:

FROM state_vendors_mat_view state
JOIN interested_vendors_mat_view iv ON (iv.vendor_id = state.vendor_id)
WHERE state.sol_id = 'abc'
GROUP BY state.vendor_id, state.sol_id

Neither of these give me the correct solution.

Any help is much appreciated. Thanks.

Answer Source

I'm not sure why your query doesn't work, but it is perhaps because the comparison in the subquery uses snid rather than sol_id (which should generate an error according to the data formats in your question).

In any case, the query would more simply be expressed using exists:

SELECT sv.*,
       (exists (select 1
                from interested_vendors_mat_view iv
                where iv.vendor_id = sv.vendor_id and iv.sol_id = 'abc'
       ) as is_interested
FROM state_vendors_mat_view sv
WHERE sv.sol_id = 'abc';