severndigital Posted July 23, 2008 Share Posted July 23, 2008 ok .. so we have this query SELECT inv.invoice_no, MAX(rel.sequence_no) AS Num_of_Lines, prd.vendor_id FROM vtiger_invoice inv INNER JOIN vtiger_crmentity crm ON inv.invoiceid = crm.crmid INNER JOIN vtiger_inventoryproductrel rel ON crm.crmid = rel.id INNER JOIN vtiger_products prd ON rel.productid = prd.productid WHERE crm.setype = 'Invoice' AND crm.deleted = 0 AND inv.duedate BETWEEN '2008-05-01' AND '2008-05-31' GROUP BY inv.invoice_no when it runs it returns a row count of 186 .. which is correct, or at least matches the hard copy count of invoices. the query includes 4 different vendor_id's (0,1355,1069,1070) the problem occurs when we run the query to ask for a specific vendor_id, using the following. SELECT inv.invoice_no, MAX(rel.sequence_no) AS Num_of_Lines, prd.vendor_id FROM vtiger_invoice inv INNER JOIN vtiger_crmentity crm ON inv.invoiceid = crm.crmid INNER JOIN vtiger_inventoryproductrel rel ON crm.crmid = rel.id INNER JOIN vtiger_products prd ON rel.productid = prd.productid WHERE crm.setype = 'Invoice' AND crm.deleted = 0 AND vendor_id = 0 AND inv.duedate BETWEEN '2008-05-01' AND '2008-05-31' GROUP BY inv.invoice_no and run that query for each of the 4 vendor ids and manually add the row counts, it comes to 205. can you see anything in the query that would cause the issue? Thanks in advance. Chris Link to comment https://forums.phpfreaks.com/topic/116263-query-with-interesting-results-any-ideas/ Share on other sites More sharing options...
fenway Posted July 23, 2008 Share Posted July 23, 2008 Which table is vendor_id in? Link to comment https://forums.phpfreaks.com/topic/116263-query-with-interesting-results-any-ideas/#findComment-598060 Share on other sites More sharing options...
severndigital Posted July 24, 2008 Author Share Posted July 24, 2008 vtiger_products (in the database) prd in the query Link to comment https://forums.phpfreaks.com/topic/116263-query-with-interesting-results-any-ideas/#findComment-598939 Share on other sites More sharing options...
fenway Posted July 26, 2008 Share Posted July 26, 2008 Sounds like you're getting some records twice...? Link to comment https://forums.phpfreaks.com/topic/116263-query-with-interesting-results-any-ideas/#findComment-600410 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.