<html><head></head><body>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<br><br><div class="gmail_quote">El 10 de noviembre de 2014 18:58:16 CET, John Crisp <john@reetspetit.net> escribió:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="k9mail">I've been bashing my head on a MySQL query this afternoon trying to do a<br />report to show sales by Product Category but cannot find the answer.<br /><br />I'm trying to find the productcategorid for a product.<br /><br />I have a query like this :<br /><br />SELECT DISTINCT DISTINCT vtiger_crmentity.crmid,<br />vtiger_invoice.invoice_no, vtiger_invoice.invoicedate,<br />vtiger_productsInvoice.productid, vtiger_productsInvoice.productname,<br />vtiger_productsInvoice.productcategory,<br />vtiger_inventoryproductrelInvoice.listprice,<br />vtiger_inventoryproductrelInvoice.quantity, vtiger_crmentity.crmid<br /> <br />FROM vtiger_invoice<br /><br />INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =<br />vtiger_invoice.invoiceid<br /><br />LEFT JOIN vtiger_inventoryproductrel AS<br />vtiger_inventoryproductrelInvoice ON vtiger_invoice.invoiceid =<br />vtiger_<a href="http://inventoryproductrelInvoice.id">inventoryproductrelInvoice.id</a><br /><br />LEFT
JOIN
vtiger_products AS vtiger_productsInvoice ON<br />vtiger_productsInvoice.productid =<br />vtiger_inventoryproductrelInvoice.productid<br /><br />WHERE vtiger_invoice.invoiceid >0<br />AND vtiger_crmentity.deleted =0<br />AND vtiger_invoice.invoicedate<br />BETWEEN '2014-01-01'<br />AND '2014-12-31'<br />AND vtiger_invoice.invoicestatus NOT LIKE 'Cancelled'<br />AND vtiger_productsInvoice.productid NOT LIKE 'Null'<br /><br /><br />This pulls up the productcategory by name, but not the ID.<br /><br />I tried adding productcategoryid in the select line but that's a fail.<br /><br />Any have any ideas ? I'm sure it is something stupid but cannot see<br />what, and can't see how that table is related.<br /><br />B. Rgds<br />John<br /><br /><br /></pre><p style="margin-top: 2.5em; margin-bottom: 1em; border-bottom: 1px solid #000"></p><pre class="k9mail"><hr /><br /><a href="http://www.vtiger.com/">http://www.vtiger.com/</a></pre></blockquote></div><br>
-- <br>
Enviado desde mi teléfono con K-9 Mail.</body></html>