* Roger Davenport
> I'm trying to do the impossible I think but what I'd like to do is join
> two tables, using the "colname" results from "a" table to lookup a
> column named "colname" in b... here we go.
I don't think you can do that, but...
> suppose we have
>
> table a:
> id colname
> 0 price1
> 1 price2
> 2 price3
>
> table b
> item price1 price2 price3
> x 1.50 2.50 3.50
> y 2.50 3.50 4.50
>
> what I'd like to do, is
>
> select b.`a.colname` from a, b where a.id=0 and b.item="x";
...how do you know a.id=0? If you mean something like this:
select b.`a.colname` from a, b where a.id=$id and b.item="$item";
...why not determine the colname in the script, and use this:
select b.$colname from b where b.item="$item";
Anyhow, if you really need to determine this in the query (per row), you
could do it using a double if():
select
if(@a=0,
b.price1,
if(@a=1,
b.price2,
b.price3)) as price
from b
where b.item="$item";
This statement selects price1 if @a=0, price2 if @a=1, otherwise price3. The
field is named "price" in any case. (This is a illustration, pointless
because @a is predefined.)
Instead of "@a=0" and "@a=1" you would probably put in some other criteria,
like "customer.discount_code in ('r1','r2','a1')" or
"report.type='pricelist'".
IF is very usefull, CASE is an alternative, both are described here:
<URL: http://www.mysql.com/doc/C/o/Control_flow_functions.html >
--
Roger
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
