Skip to Content
This question has been flagged
sqljoin
1 Respondre
4783 Vistes
Avatar
Grf

I made this SQL query and got sale orders that I needed, but I still need to join this sale orders with their invoices and filter only those sale orders that have invoices with state != 'draft'.

but can't find a way to join with sale orders with invoices.

I think there is table called sale_order_invoice_rel but can't figure out how to join and filter correctly

SELECT DISTINCT so.id

FROM stock_picking sp

INNER JOIN stock_move sm

ON sp.id = sm.picking_id

INNER JOIN procurement_order po

ON sm.procurement_id = po.id

INNER JOIN sale_order_line sol

ON po.sale_line_id = sol.id

INNER JOIN sale_order so

ON sol.order_id = so.id

WHERE so.invoice_status = 'to invoice' and sp.state = 'done'
ORDER BY so.id ASC
Avatar
Descartar
Avatar
Michael Judd
Best Answer 
Could something like this work for you? (I know this is an old thread - but when solving this for my own needs I came upon it)

SELECT so.name as orderno,  p.name as partner_name, so.client_order_ref, so.amount_untaxed, so.amount_total, aml.name, aml.move_name as invoice 
FROM public.sale_order so 
LEFT JOIN public.sale_order_line sol on aml.id = olr.invoice_line_id
WHERE so.state = 'sale' and so.invoice_status = 'invoiced' 
ORDER BY p.name ASC, so.amount_total DESC, aml.name ASC

Avatar
Descartar
Nikhil Nakrani

Hi grf,
Can you try this way,

SELECT DISTINCT so.id
FROM stock_picking sp
INNER JOIN stock_move sm
ON sp.id = sm.picking_id
INNER JOIN procurement_order po
ON sm.procurement_id = po.id
INNER JOIN sale_order_line sol
ON po.sale_line_id = sol.id
INNER JOIN sale_order so
ON sol.order_id = so.id
INNER JOIN sale_order_invoice_rel rel
ON so.id = rel.order_id
INNER JOIN account_invoice ai
ON rel.invoice_id = ai.id
WHERE so.invoice_status = 'to invoice'
AND sp.state = 'done'
AND ai.state != 'draft'
ORDER BY so.id ASC

Related Posts Respostes Vistes Activitat
Usage Of SQl Queries In odoo
sql
Avatar
Avatar
1
de nov. 22
7765
sql constraints aren't being detected in my model
sql
Avatar
Avatar
1
d’oct. 21
4457
Odoo on microsoft sql server instead postgres sql?
sql
Avatar
2
de nov. 18
11122
Openerp with MS-SQL
sql
Avatar
Avatar
Avatar
Avatar
4
de març 15
6230
how can i get the date in dd mm yyyy format please help me?
sql
Avatar
Avatar
1
de març 15
6925