Filter self joined and include only rows that dont match nested join

Question

I have the following pseudo tables: (PostgresSQL)

Payment
    id: number;
    groupId: number;
    status: [Pending|Executed];

PaymentLog
    id: number;
    paymentId: number;
    date: Date; // YYYY-DD-MM

• Fetch all Payment with Pending
• But exclude Payment if a row in PaymentLog referencing a Payment under same groupId and matching a specific date

I have tried something like

SELECT p1.id FROM Payment
JOIN Payment p2 ON p1.groupId = p2.groupId
LEFT JOIN PaymentLog p3 ON p2.id = p3.paymentId AND date = '2020-09-06'
WHERE
   p3.id IS NULL
GROUP BY p2.id

But this will also match if PaymentLog has items matching groupId in referencing Payment that does not match the date.

Answer 1

I suspect that you want not exists:

select p.* 
from payment p
where status = 'Pending' and not exists (
    select 1
    from paymentlog pl1
    inner join payment p1 on p1.paymentid = pl1.paymentid
    where p1.groupid = p.groupid and pl1.date = date '2020-09-06'
)