[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Query help...
From:       Michael Heaney <mheaney () jcvi ! org>
Date:       2012-02-29 18:49:09
Message-ID: 4F4E7325.7040701 () jcvi ! org
[Download RAW message or body]

On 2/29/2012 1:15 PM, Don Wieland wrote:
> Little help...
>
> In my mySQL query editor, I am trying to return a value of 0 when
> there is no related rows from this query:
>
> (select if(count(ip.payment_amount) IS NOT NULL,
> count(ip.payment_amount) , 0)  FROM tl_trans_pmt_items ip WHERE
> t.transaction_id = ip.inv_id GROUP BY ip.inv_id) as d,
>
> regardless of the combination I use, invalid relationships come back
> as NULL - need to return 0  so I can use it in a math formula.
>
> Probably simple - maybe ;-)
>
> Don
>


I think you want the 'coalesce' function:

Syntax:
COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no
non-NULL values.

URL: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html

Examples:
mysql> SELECT COALESCE(NULL,1);
         -> 1


Michael Heaney
JCVI


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

[prev in list] [next in list] [prev in thread] [next in thread]