How to make a complex query on multiple tables?

Question

PostgreSQL

With fairly simple queries, it comes out simply, but there is this type of
Input data, tables:

sale_order, stock_picking, sale_order_line, account_move_line, account_move

Relationships between tables:

sale_order_line.order_id = many2one(sale_order)
sale_order_line.invoice_lines = many2many(account_move_line)
sale_order_line_invoice_rel(invoice_line_id, order_line_id)

stock_picking.sale_id = many2one(sale_order)
account_move_line.move_id = many2one(account_move)

By what rules do I need to make a selection:
Select all sale_orders with sale_order_line.qty_delivered = 0 and sale_order_line.qty_invoiced and stock_picking > 2 and account_move >

2

select so.name
    from sale_order so
        left join stock_picking on so.id = stock_picking.sale_id
        left join sale_order_line as sol on sol.order_id = so.id
        left join sale_order_line_invoice_rel as solir on sol.id = solir.order_line_id
        left join account_move as am on am.id = solir.invoice_line_id
        group by so.name having count(*) > 2

The difficulty lies in the correctness of the selection and verification of the number of selected objects.
Thank you all in advance for your participation.

Sampling on the example of a specific object is correct

select so.name, sol.name, solir.invoice_line_id, sp.id, solir.invoice_line_id
    from sale_order so
        left join sale_order_line as sol on sol.order_id = so.id
        full outer join sale_order_line_invoice_rel as solir on solir.order_line_id = sol.id
        left join stock_picking as sp on so.id = sp.sale_id
    where so.id = 5924
group by so.name, sol.name, sp.id, solir.invoice_line_id

now I remove where so.id = 5924 and I need to make a condition if sp.id > 2 AND solir.invoice_line_id > 2 AND sol.qty_delivered = 0 AND sol.qty_invoiced = 0

I can't figure out what and where to add for this?

Answer 1

First, I would form a query containing ALL fields participating in conditions like
SELECT TOP 20 DISTINCT {All fields participating in conditions} FROM T
LEFT JOIN {All tables} ON {Relation conditions}
After I would make sure that the query receives the correct data, I would converted it
SELECT TOP 20 DISTINCT {All fields participating in conditions} {Function for having} FROM T
LEFT JOIN {All tables} ON {Relationship conditions}
group by {All fields participating in conditions}
Again, make sure that {Function for having} returns that what should be and add
having {Function for having}