[Vtigercrm-developers] MySQL query

[John Crisp]

I've been bashing my head on a MySQL query this afternoon trying to do a
report to show sales by Product Category but cannot find the answer.

I'm trying to find the productcategorid for a product.

I have a query like this :

SELECT DISTINCT DISTINCT vtiger_crmentity.crmid,
vtiger_invoice.invoice_no, vtiger_invoice.invoicedate,
vtiger_productsInvoice.productid, vtiger_productsInvoice.productname,
vtiger_productsInvoice.productcategory,
vtiger_inventoryproductrelInvoice.listprice,
vtiger_inventoryproductrelInvoice.quantity, vtiger_crmentity.crmid
	
FROM vtiger_invoice

INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
vtiger_invoice.invoiceid

LEFT JOIN vtiger_inventoryproductrel AS
vtiger_inventoryproductrelInvoice ON vtiger_invoice.invoiceid =
vtiger_inventoryproductrelInvoice.id

LEFT JOIN vtiger_products AS vtiger_productsInvoice ON
vtiger_productsInvoice.productid =
vtiger_inventoryproductrelInvoice.productid

WHERE vtiger_invoice.invoiceid >0
AND vtiger_crmentity.deleted =0
AND vtiger_invoice.invoicedate
BETWEEN  '2014-01-01'
AND  '2014-12-31'
AND vtiger_invoice.invoicestatus NOT LIKE 'Cancelled'
AND vtiger_productsInvoice.productid NOT LIKE 'Null'


This pulls up the productcategory by name, but not the ID.

I tried adding productcategoryid in the select line but that's a fail.

Any have any ideas ? I'm sure it is something stupid but cannot see
what, and can't see how that table is related.

B. Rgds
John


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20141110/3b9747c5/attachment.pgp>