Mutually Exclusive Values In Sql

I have a query which selects products from a table. A product can have multiple prices (think of various prices) and a default price. Naturally, this is a one-to-many relation. I

Solution 1:

If I understand your question correctly, the products table would have the default price and the product_prices table would have any other price.

You want to know where the default price is being used, meaning that there are no other prices. For this, use a left outer join:

SELECT p.*, coalesce(pp.price, p.default_price)
FROM products p LEFTOUTERJOIN 
     products_prices pp
     ON p.id = pp.productId
WHERE pp.price = GIVENPRICE or pp.price isnull

Based on your comment, you are storing the default prices in records with the business id being NULL. In this case, I would do two joins to the prices table:

SELECT p.*, coalesce(pp.price, defpp.price)
FROM products p LEFTOUTERJOIN 
     products_prices pp
     ON p.id = pp.productId and pp.price = GIVENPRICE leftouterjoin
     products_prices defpp
     on p.id = defpp.productId and defpp.businessId isNULL

The first join gets the price matching the given price. The second gets the default price. The first result is used, if present, otherwise the second is used.