List:General Discussion« Previous MessageNext Message »
From:Jonathan Mangin Date:September 24 2005 9:54pm
Subject:Re: Using ifnull in a subquery
View as plain text  
----- Original Message ----- 
From: "Jonathan Mangin" <jon.mangin@stripped>
To: <mysql@stripped>
Sent: Saturday, September 24, 2005 2:18 PM
Subject: Using ifnull in a subquery


> Hello all,
> 
> This works if t2 is populated:
> 
> select t1.item_no, t1.value1 *
> (select weight from t2 where
> item_no = t1.item_no and
> descrip = 'dime bag')
> from t1
> where t1.descrip = 'marigold seeds'
> 
> If t1.item_no and/or 'dime bag' don't exist in t2 I'd
> like to multiply by a different value (5):
> 
> select t1.item_no, t1.value1 *
> (select ifnull(weight,5) from t2 where
> item_no = t1.item_no and
> descrip = 'dime bag')
> from t1
> where t1.descrip = 'marigold seeds'
> 
> My tests return NULL instead of t1.value1 * 5.
> Is there a way to do what I need?
> 
> Thanks,
> Jon
> 

Never mind, it's pretty obvious...

select t1.item_no, t1.value1 *
ifnull((select weight from t2 where
item_no = t1.item_no and
descrip = 'dime bag'),5)
from t1
where t1.descrip = 'marigold seeds'

Thread
Using ifnull in a subqueryJonathan Mangin24 Sep
  • Re: Using ifnull in a subqueryJonathan Mangin24 Sep