SQL - Find Master Records That Do NOT share a value with Detail Records-CodePudding

This question may have been asked, but I am not sure how to word this in a summary, so I may have missed it.

My situation:

I have a Master Record (simplified):

   ServiceOrder
     ID         - String (15)
     Status     - String (1)

Each Master has detail records

SODetail
 ID           - String (15)
 LineNbr      - int 
 Status       - String (CP)

SODetail and Master join ON column ID

Status is closed in Master when there is a value of "C"

Status is closed in SODetail when there is a value of "CP"

I need to have a SQL command that will find all of the Master Records, whose value is NOT "C" but where all of its SODetail records have a status of "CP"

In other words, something happened where some Service Orders did not get set to closed when all of its detail records were set to closed. I need to find them so we can fix them.

I know there is a command that can give me this, but somehow it eludes me.

CodePudding user response：

The trick here is to work backwards.

The first task is to determine which IDs have all their status as CP

SELECT  ID
FROM    SODetail
GROUP BY ID
HAVING  COUNT(*) = SUM(CASE WHEN Status = 'CP' THEN 1 ELSE 0 END)

The above is a list of IDs with only 'CP' as their status. It does this by counting the number of detail records, and counting the number of detail records with 'CP' as their status, and returning the IDs where these counts are the same.

Having the relevant IDs from above, you can easily just join this to your master record and filter by the master record's status.

SELECT  SO.ID
FROM    ServiceOrder AS SO
        INNER JOIN 
            (SELECT   ID
              FROM    SODetail
              GROUP BY ID
              HAVING  COUNT(*) = SUM(CASE WHEN Status = 'CP' THEN 1 ELSE 0 END)
            ) AS SOD ON SO.ID = SOD.ID
WHERE   SO.Status <> 'C'

Edit: More explanation and some clarifications