Jump to content

Query with interesting results ... any ideas


severndigital

Recommended Posts

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.