----- Original Message ----- From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com> 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' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]