Query with interesting results ... any ideas

[severndigital]

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[fenway]

Which table is vendor_id in?

[severndigital]

vtiger_products (in the database)

prd in the query

[fenway]

Sounds like you're getting some records twice...?