Postgres - Where condition on nested joins table not working
The inner join between invoices and invoice_items turns the outer join between order_items as invoice_items back into an inner join. You need an outer join for invoices as well. And then you need to move the restriction on the invoice state into the join condition. 

SELECT oi.*
FROM order_items oi
  LEFT JOIN invoice_items ii
         ON ii.order_item_id = oi.id
  LEFT JOIN invoices inv 
         ON inv.id = ii.invoice_id AND inv.state &lt;&gt; 'cancelled'
GROUP BY oi.id
HAVING (booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity),0)) &gt; 0
ORDER BY oi.id ASC;


Maybe an EXISTS query is what you are looking for: 

select oi.*
from order_items oi
where exists (select ii.order_item_id
              from invoices inv 
                join invoice_items ii ON inv.id = ii.invoice_id 
              where ii.order_item_id = oi.id
                and inv.state &lt;&gt; 'cancelled'
              group by ii.order_item_id
              having booked_quantity - cancelled_quantity - coalesce(sum(ii.quantity), 0) &gt; 0)
order by oi.id asc;