"IF NOT found" in query

Hi,
I have a table which holds the prices of a certain good in all currencies we support. As there are over 200 countries, we only have the pricing of this good for a limited number of currencies. Is there an elegant way to query the DB such that if it does not find the pricing for a certain country it returns the default pricing?

In other words if my customer is from Vietnam and we do not support the local currency, then the retured result should be the pricing in US dollars, for example.

Thanks,
Adrien


SELECT 
    products.id
  , products.name
  , COALESCE(p1.price, p2.price)
FROM products
LEFT OUTER JOIN prices AS p1
ON products.id = p1.id
AND p1.currency = 'your clients currency here'
LEFT OUTER JOIN prices AS p2
ON products.id = p2.id
AND p2.currency = 'your default currency here'

Hi Guido,
Thank you very much for your quick feedback. I apologise for not specifying that all the necessary data is included in one table and the actual table structre itself:

CREATE TABLE pricing (
  id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  currency char(3) NOT NULL,
  currency_symbol char(1) NOT NULL,
  price smallint(5) unsigned NOT NULL,
  country char(2) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY price (price),
  KEY country (country)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Thanks,
Adrien

And how do you know what price a product has? There must be some link between product and price?

Anyway, try


SELECT 
   COALESCE(p2.price, p1.price)
FROM prices AS p1
LEFT OUTER JOIN prices AS p2
ON p1.id = p2.id
AND p2.currency = 'your clients currency here'
WHERE p1.currency = 'your default currency here'

guido, there is only one product :slight_smile:

also, in your query, you’re matching two rows based on an auto_increment number, so you’re really only joining a row to itself, and it can’t possibly have different currencies

Hi Guido,
Thanks for the new query.
Please bear in mind that there is ony one product and that the pricing table contains the currency and the price. Currently to decide if I have a price for a particular customer i check the two digit ISO country code.

I have also tested the query and it does not seem to work.

Thanks,
Adrien

Ahh yes :smiley:
How about


SELECT 
   COALESCE(p2.price, p1.price) AS price
FROM prices AS p1
LEFT OUTER JOIN prices AS p2
ON p2.currency = 'your clients currency here'
WHERE p1.currency = 'your default currency here'

Hi Guido,
With a small tweak it seems to be working. I forgot to mention that there are multiple prices for the same product depending on quantity (my bad) so I needed to wrap a DISTINCT around the COALESCE. Here is the working query:

SELECT 
   DISTINCT (COALESCE(p2.price, p1.price)) AS price
FROM pricing AS p1
LEFT OUTER JOIN pricing AS p2
ON p2.country = 'customer country'
WHERE p1.country = 'default country'

Glad you got it to work, and glad I finally managed to understand the problem (thanks Rudy :slight_smile: ).

Thanks to both of you. Great work!