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.
Best Answer
The condition by left table converts your RIGHT JOIN to INNER JOIN implicitly.
Use, for example, something like (taking into account the former comment):
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).