View Single Post
 
Old June 13th, 2007, 08:46 AM
jwhite68 jwhite68 is offline
Registered User
 
Join Date: Jun 2007
Location: Varna, , Bulgaria.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was being a numpty and reading my results incorrectly. The SQL code I posted does work. Its just that I had many records with 0 price in my test data that I forgot about.

So I worked out the solution as:

SELECT price,currency,CAST((price*rates.rate) AS UNSIGNED) AS priceval
FROM products, rates
WHERE currency=rates.from_code AND
rates.to_code=0
ORDER BY priceval ASC

If this helps anyone else with a similar problem, then great.