[Vtigercrm-developers] MySQL query

[Joe Bordea]

Not quite sure what you need the id for but the product category id is in the vtiger_productcategory table so you are missing a join with that table

El 10 de noviembre de 2014 18:58:16 CET, John Crisp <john at reetspetit.net> escribió:
>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
>
>
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>http://www.vtiger.com/

-- 
Enviado desde mi teléfono con K-9 Mail.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20141110/2a1fd59a/attachment.html>