Return single row with one to many relationship based on evaluated conditions

Question

Database is Postgres.

I have an issue whereby I have a table with the following columns: ID, Product_ID Customer_ID, Price and Item_Type.

This table is joined on Product_ID to another table. For a Given product ID there is the default price (where the Customer_ID is Blank) for a given item type (New, Renewal, Redemption etc.), there may also be a customer specific price list (where Customer_ID is joined to the Customers Table).

The issue is that this Database has been poorly maintained. For Item_Type there are 2 Prices that for this issue we are interested: Renew and Renewal. Some prices have a Renew price, some have a Renewal Price and some have both.

select p.id, pr.customer_id, p.period, pr.price, pr.item_type
from products p
right join prices pr on
pr.product_id = p.id and pr.period = p.period and pr.customer_id is null
where p.status = 'PURCHASED'

As an example this produces the following:

121751  8407508 12 29.95 RENEW
121751  8407508 12 35.95 RENEWAL

The cause is that this product has 2 different prices for Renew and Renewal - looking at the front end application code - if there is an item_type 'RENEWAL' then this price is used, if not then the 'RENEW' price is used.

I've tried using a case statement, both in the select and in a where clause:

case pr.item_type 
    when 'RENEWAL' then pr.price
    when 'RENEW' then pr.price
    end

But I still get both rows - I'm not sure how to do a conditional and hierarchical lookup e.g. if Condition A is matched, then return row that matches Condition A, if no match is found, Condition B is matched, then return row that matches Condition B etc.

Answer 1

from products p right join prices pr ... where p.status = 'PURCHASED'

The condition by left table converts your RIGHT JOIN to INNER JOIN implicitly.

if there is an item_type 'RENEWAL' then this price is used, if not then the 'RENEW' price is used

Use, for example, something like (taking into account the former comment):

SELECT ..., COALESCE(t1.price, t2.price) AS price, ...
FROM products 
LEFT JOIN prices AS t1 ON t1.item_type = 'RENEWAL' AND {another joining conditions} 
LEFT JOIN prices AS t2 ON t2.item_type = 'RENEW' AND {another joining conditions} 
WHERE {another conditions}

IF RENEWAL price exists then it will be returned, if not, but RENEW price exists then you'll get it, else you'll get NULL (you may join more price table copies with another price types in needed order).