On 8 May 2007 06:16:08 -0700, drinian <AdamKrell@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) ?