SQL JOIN get all records that do not match certain criteria-CodePudding

I have two tables Order and Invoice.

Order can have multiple invoices. Each invoice record has a state - paid or unpaid.

Order    Invoice
O-123.     i1 (paid)
O-123.     i2 (unpaid)
O-123.     i3(unpaid)
O-456      i4(paid)
O-456      i4(paid)
O-678.     i5 (paid)
O-678      i6 (paid)

I need to get a list of all order which have no unpaid invoice. In this case it should return o456 and o678. Sample query

select * from core.order as o
inner join
invoices as inv
on o.id = inv.order_id
where inv.status is paid

CodePudding user response：

You can use not exists for that. (Assumed datatype of status column as varchar)

select * from core.order as o
where not exists 
                (
                 select 1 from invoices as inv where status='unpaid' and o.id=inv.order_id
                )

CodePudding user response：

One canonical approach uses aggregation:

SELECT o.id
FROM core.order o
LEFT JOIN invoices inv
    ON inv.order_id = o.id
GROUP BY o.id
HAVING COUNT(CASE WHEN inv.status = 'unpaid' THEN 1 END) = 0;

CodePudding user response：

One method is using not exists

select * 
from core.order o
where not exists (
   select 1 
   from invoices as inv
   where o.id = inv.order_id and inv.status is 'unpaid' 
)