On May 8, 10:07 am, lark <ham...@sbcdeglobalspam.net> wrote:
> drinian 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)?
>
> > Also, is this what a crosstab query is?
>
> > I appreciate any advice or input.
>
> looks like you're using a fourth table called invoiceitem. would you be
> able to post the output of these commands:
> desc Invoices;
> desc InvoiceStatus;
> desc SaleItem;
> desc InvoiceItem;
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".

Opps, that's right. I forgot about InvoiceItems. Here's the
descriptions:

mysql> desc Invoices;
+------------------+---------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------+---------------+------+-----+---------
+----------------+
| InvoiceNumber | bigint(20) | | PRI | NULL |
auto_increment |
| JobID | bigint(20) | YES | MUL | 0
| |
| AssignedDate | datetime | YES | | NULL
| |
| ServiceDate | datetime | YES | | NULL
| |
| BillName | varchar(50) | YES | | NULL
| |
| BillAddress | varchar(50) | YES | | NULL
| |
| BillCityID | bigint(20) | YES | MUL | 0
| |
| BillState | varchar(50) | YES | | NULL
| |
| BillZip | varchar(50) | YES | | NULL
| |
| ServiceName | varchar(50) | YES | | NULL
| |
| ServiceAddress | varchar(50) | YES | | NULL
| |
| ServiceCityID | bigint(20) | YES | MUL | 0
| |
| ServiceState | varchar(50) | YES | | NULL
| |
| ServiceZip | varchar(50) | YES | | NULL
| |
| InvoiceStatusID | bigint(20) | YES | MUL | 0
| |
| Route | bigint(20) | YES | | 0
| |
| TermsID | bigint(20) | YES | MUL | 0
| |
| Subtotal | decimal(20,4) | YES | | 0.0000
| |
| SalesTax | decimal(20,4) | YES | | 0.0000
| |
| TotalCharge | decimal(20,4) | YES | | 0.0000
| |
| Taxable | tinyint(4) | YES | | NULL
| |
| TaxItemID | bigint(20) | YES | MUL | 0
| |
| Notes | text | YES | | NULL
| |
| Recorded | tinyint(4) | YES | | 0
| |
| RecordedDate | datetime | YES | | NULL
| |
| RecordedIncomeID | bigint(20) | YES | | NULL
| |
+------------------+---------------+------+-----+---------
+----------------+
26 rows in set (0.00 sec)

mysql> desc InvoiceStatus;
+-----------------+-------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------------+-------------+------+-----+---------
+----------------+
| InvoiceStatusID | bigint(20) | | PRI | NULL |
auto_increment |
| InvoiceStatus | varchar(50) | YES | | NULL
| |
+-----------------+-------------+------+-----+---------
+----------------+
2 rows in set (0.00 sec)

mysql> desc SaleItem;
+---------------------+-------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+---------------------+-------------+------+-----+---------
+----------------+
| SaleItemID | bigint(20) | | PRI | NULL |
auto_increment |
| SaleItem | varchar(50) | YES | | NULL
| |
| SaleItemDescription | varchar(50) | YES | | NULL
| |
| Taxable | tinyint(4) | YES | | 1
| |
+---------------------+-------------+------+-----+---------
+----------------+
4 rows in set (0.00 sec)

mysql> desc InvoiceItem;
+-----------------+---------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------------+---------------+------+-----+---------
+----------------+
| InvoiceItemID | bigint(20) | | PRI | NULL |
auto_increment |
| InvoiceNumber | bigint(20) | YES | MUL | 0
| |
| SaleItemID | bigint(20) | YES | MUL | 0
| |
| ItemDescription | varchar(50) | YES | | NULL
| |
| Taxable | tinyint(4) | YES | | 1
| |
| Charge | decimal(20,4) | YES | | 0.0000
| |
+-----------------+---------------+------+-----+---------
+----------------+
6 rows in set (0.00 sec)

Thank you for the help.