On 9 May, 13:42, drinian <AdamKr...@gmail.com> wrote:
> On May 9, 1:28 am, 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) ?
>
> No, changing it to a LEFT JOIN returns equal amounts for each sale
> item (like lark's suggestion). Changing lark's statement to be a LEFT
> JOIN returns the correct amounts but will not show zero totals for
> sale items with no invoices.
>
> It's got me stumped.- Hide quoted text -
>
> - Show quoted text -

Can you export the table schema with a bit of sample data and I'll
load it up and take a look for you.