Well, it depends on what exactly you mean by 'checking' Bfn1, Bfn2
and Bfn3, but you should be able to do something like this (assuming
for this example that you want only want to return results where
Bfn1/Bfn2/Bfn3 is equal to 99):
select *
from table_a as A, table_b as B
where
(case A.Afn when 1 then B.Bfn1 when 2 then B.Bfn2 when 3 then B.Bfn3 end)
= 99
Or you could nest IFs:
select *
from table_a as A, table_b as B
where
if(A.Afn = 1, B.Bfn1, if(A.Afn = 2, B.Bfn2, B.Bfn3)) = 99
Neither of these does error checking if A.Afn not in {1,2,3}.
See
http://www.mysql.com/doc/en/Control_flow_functions.html
for more info.
-steve
At 12:33 PM +1000 10/7/02, "Peter Goggin" <[EMAIL PROTECTED]> wrote:
>This requires a similar function to Oracles decode. I do not know if MySQL
>provides such a function.
>
>Regards
>
>Peter Goggin
>
>----- Original Message -----
>From: "Alex Shi" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Monday, October 07, 2002 6:47 AM
>Subject: where clause question
>
>
>> Hi,
>>
>> I need a where clause in following situation:
>>
>> Say I want to query two tables: A and B. In table A there is field Afn,
>> while in table B there ere 3 fields: Bfn1, Bfn2 and Bfn3. I want to
>> compose a query, in which the where clause can do this:
>>
>> if A.Afn=1, then check Bfn1,
>> if A.Afn=2, then check Bfn2,
>> if A.Afn=3, then check Bfn3.
>>
>> So how I compose a where clause to do this? Thanks in advance!
>>
> > Alex Shi
>>
--
+------------------------------------------------------------------------+
| Steve Edberg [EMAIL PROTECTED] |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| The end to politics as usual: |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
+------------------------------------------------------------------------+
---------------------------------------------------------------------
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
