On 9 May, 06:28, subtenante <zzsubtenant...@gmail.com> wrote:
> On 8 May 2007 06:16:08 -0700, drinian <AdamKr...@gmail.com> wrote:
>
>
>
>
>
> >I have three tables: Invoices, InvoiceStatus, SaleItem
>
> >I want to run a query that totals the invoices by the sale item.
> >Here's my query:
>
> >SELECT Sum(Invoices.TotalCharge) AS Amount, SaleItem.SaleItem,
> >SaleItem.SaleItemID FROM SaleItem RIGHT JOIN ((Invoices LEFT JOIN
> >InvoiceStatus ON Invoices.InvoiceStatusID =
> >InvoiceStatus.InvoiceStatusID) LEFT JOIN InvoiceItem ON
> >Invoices.InvoiceNumber = InvoiceItem.InvoiceNumber) ON
> >SaleItem.SaleItemID = InvoiceItem.SaleItemID WHERE
> >InvoiceStatus.InvoiceStatus != 'Void' AND MONTH(ServiceDate) = '02'
> >AND YEAR(ServiceDate) = '2007' GROUP BY SaleItem.SaleItem ORDER BY
> >SaleItem.SaleItem
>
> >This works fine, but I would like the query to show a zero value for
> >the sale items not purchased. Currently this query will only show the
> >totals of actual sale items that were sold. Is there a way to force
> >the query to always show all sales items, even the ones that total to
> >zero (no sale item sold that month)?
>
> Maybe i got something wrong, but it seems to me you are using a RIGHT
> JOIN just after your FROM SaleItem. If you want to be sure to have all
> the SaleItems represented at least once, should it not be a LEFT JOIN
> (in which case you might get a NULL instead of 0 in your Amount
> column) ?- Hide quoted text -
>
> - Show quoted text -

But a NULL instead of 0 can be solved with an IFNULL(`column`,0)