MySQL here. I have the following data model:

[applications]
===
id : PK
status : VARCHAR
...lots of other fields

[invoices]
===
id : PK
application_id : FK to applications.id
status : VARCHAR
... lot of other fields

It is possible for the same application to have 0 invoices associated with it, each with a different status. I am trying to write a query that looks for applications that:

1. have a status of "Pending"; and
2. have only invoices whose status is "Accepted"

My best attempt at such a query is:

SELECT a.id,
       i.id,
       a.status,
       i.status
FROM application a
INNER JOIN invoice i ON  a.id = i.application_id
WHERE a.status = "Pending"
AND i.status = "Accepted"

The problem here is that this query does not exclude applications that are associated with non-Accepted invoices. Hence it might return a row of, say:

 -------- -------- ----------- ----------- 
| id     | id     |  status   |  status   |
 -------- -------- ----------- ----------- 
| 123    | 456    |  Pending  |  Accepted |
 -------- -------- ----------- ----------- 

However, when I query the invoice table for any invoices tied to application id = 123, there are many non-Accepted invoices that come back in the results. Its almost as if I wished SQL support some type of "AND ONLY HAS" so I could make my clause: "AND ONLY HAS i.status = 'Accepted'"

So I'm missing the clause that excludes results for applications with 1 non-Accepted invoices. Any ideas where I'm going awry?

CodePudding user response：

You can use the following logic:

SELECT *
FROM application
WHERE status = 'pending'
AND EXISTS (
    SELECT 1
    FROM invoice
    WHERE invoice.application_id = application.id
    HAVING SUM(CASE WHEN invoice.status = 'accepted' THEN 1 ELSE 0 END) > 0 -- count of accepted invoices > 0
    AND    SUM(CASE WHEN invoice.status = 'accepted' THEN 0 ELSE 1 END) = 0 -- count of anyother invoices = 0
)