List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:October 7 2002 10:27am
Subject:Re: where clause question
View as plain text  
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" <pgoggin@stripped> 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" <chpshi@stripped>
>To: <mysql@stripped>
>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                                      sbedberg@stripped |
| 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/) |
+------------------------------------------------------------------------+
Thread
where clause questionAlex Shi7 Oct
  • Re: where clause questionPeter Goggin7 Oct
    • Re: where clause questionSteve Edberg7 Oct