SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    "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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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'

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    And how do you know what price a product has? There must be some link between product and price?

    Anyway, try
    Code:
    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'

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    guido, there is only one product

    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    Ahh yes
    How about
    Code:
    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'

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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'

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Glad you got it to work, and glad I finally managed to understand the problem (thanks Rudy ).

  10. #10
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Italy
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to both of you. Great work!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •