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.