SQL querying through a many to many relationship

I have a many to many relationship represented by three tables: quote, payment and quote_payment since a quote can have several payment attempts, and a payment can be for multiple quotes. My goal is to make a query that’ll return all quotes who have or haven’t been paid.

So if a quote has at least one payment where is_paid = true it should show up once on my query for quotes that have been paid, and if it doesn’t, it should show up when I’m trying to get the quotes that haven’t been paid.

Currently, I have this for the query that shows the quotes that have been paid:

select distinct quote.name, payment.is_paid from quote
join quote_payment on (quote.id = quote_payment.quote_id)
join payment on (payment.id = quote_payment.payment_id)
where payment.is_paid is true;

And this for the ones that haven’t been paid:

select distinct quote.name, payment.is_paid from quote
join quote_payment on (quote.id = quote_payment.quote_id)
join payment on (payment.id = quote_payment.payment_id)
where payment.is_paid is false;

The issue is when I try to get the quotes that haven’t been paid. If a quote has, for example, a payment that failed and another one that was successful it shouldn’t be shown on the queries for the quotes that haven’t been paid.

I’ve also tried to put the is_paid column in quote, instead of payment, but this would require me to update all quotes related to a payment whenever a payment is successful, but maybe that’s what I should be doing. With this schema in mind, how would you go on about getting the quotes that don’t have any successful payments?

Here’s the schema with example data and some attempts I’ve made.

Answer

Use aggregation. To return a flag with each payment:

select q.name, max(p.is_paid) as is_paid
from quote q join
     quote_payment qp
     on q.id = qp.quote_id join
     payment p
     on p.id = qp.payment_id
group by q.name;

To filter, use a having clause . . . having max(p.is_paid) = 0 for instance.