Postgres Question

[Christopher Browne]

On 6/29/05, Lance F. Squire <lance-5ZoueyuiTZhBDgjK7y7TUQ at public.gmane.org> wrote:
> I need to search on the $ value of something in a table where multiple
> currencies are allowed, and the currency conversion rates are in another
> table.
> 
> For instance, on EBay, things can be listed in US, CDN or GBP etc...
> 
> Assuming the table has these fields:
> 
> Value | Currency
> -----------------
> 10.00 | US
> 25.00 | CDN
> 30.00 | GBP
> 
> and the conversion table had these fields:
> 
> Currency | Conversion
> ----------------------
> US       | 1
> CDN      | .75
> GBP      | .55
> 
> (Yes, values ARE guessed at here. ;) )
> 
> What would be the sainest way to search for anything less than $x CDN?

Something akin to...

/* cbbrowne@[local]/dba2 la=*/ select * from amounts;
 value | curr 
-------+------
 10.00 | US
 25.00 | CDN
 30.00 | GBP
(3 rows)

/* cbbrowne@[local]/dba2 la=*/ select * from currs;
 curr | conversion 
------+------------
 US   |       1.00
 CDN  |       0.75
 GBP  |       0.55
(3 rows)

/* cbbrowne@[local]/dba2 la=*/ select a.*, a.value / c.conversion from
amounts a, currs c where c.curr = a.curr ;
 value | curr |      ?column?       
-------+------+---------------------
 10.00 | US   | 10.0000000000000000
 25.00 | CDN  | 33.3333333333333333
 30.00 | GBP  | 54.5454545454545455
(3 rows)


-- 
http://www3.sympatico.ca/cbbrowne/linux.html
"The true  measure of a  man is how he treats  someone who can  do him
absolutely no good." -- Samuel Johnson, lexicographer (1709-1784)
--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml