I`ve been asked to include NOT INVOICED orders into Invoices Analysis but when I include pos_orders_line the query returns a huge ammount of rows.
In Invoices Analysis the original query below:
FROM account_invoice_line ail
JOIN account_invoice ai ON ai.id = ail.invoice_id
JOIN res_partner partner ON ai.commercial_partner_id = partner.id
LEFT JOIN product_product pr ON pr.id = ail.product_id
LEFT JOIN product_template pt ON pt.id = pr.product_tmpl_id
LEFT JOIN product_uom u ON u.id = ail.uos_id
# returns 12.627 rows
# my query returns 127.558 rows:
from pos_order_line as l
JOIN (select * from pos_order where state = 'done') s ON (s.id=l.order_id)
So why it returns almost 4.000.000 when I join them?
Select ai.id FROM account_invoice_line ail
JOIN account_invoice ai ON ai.id = ail.invoice_id
JOIN res_partner partner ON ai.commercial_partner_id = partner.id
LEFT JOIN product_product pr ON pr.id = ail.product_id
left JOIN product_template pt ON pt.id = pr.product_tmpl_id
LEFT JOIN product_uom u ON u.id = ail.uos_id
join pos_order_line as l on l.product_id = pr.id
join (select * from pos_order where state = 'done') s
on (s.id=l.order_id)
