代码之家 › 专栏 › 技术社区 › Chaban33

内联发票

join sql

1

Chaban33 · 技术社区 · 7 年前

我进行了此SQL查询并获得了所需的销售订单,但我仍然需要将此销售订单与其发票关联起来,并仅过滤具有发票的销售订单 state != 'draft' .

我想有张桌子叫 sale_order_invoice_rel 但无法正确地加入和筛选

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

1 回复 | 直到 7 年前

1

Charif DZ 7 年前

你可以像这样加入他们。

    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
                       -- keep join them like this
                       INNER JOIN sale_order_invoice_rel so_inv_rel on so.id = so_inv_rel.order_id
                       INNER JOIN account_invoice inv on so_inv_rel.invoice_id = inv.id

    WHERE 

            so.invoice_status = 'to invoice'
            and sp.state = 'done'       
            and inv.state != 'draft'

使用 INNER JOIN 将仅显示具有发票的销售订单。