(SQL) Want to know request numbers that exist with one value but not another

I’m querying a single table that has a column (SignatureDocumentType) with two possible outcomes ‘PreliminaryTestReport’ and ‘FinalTestReport’. When either exists for a single RequestID a new row is created. I want to know all RequestIDs where ‘PreliminaryTestReport’ exists but ‘FinalTestReport’ doesn’t exist for the same RequestID.

When I run the below I return no values and I know there is at least 1 that fits the criteria

select RequestID from RequestDocumentSignature 
where not exists (select RequestID from RequestDocumentSignature where SignatureDocumentType = 'FinalTestReport') and 
exists (select RequestID from RequestDocumentSignature where SignatureDocumentType = 'PreliminaryTestReport')
group by RequestID

>Solution :

There is no need for the second exists. You can apply where for the table (outer query) itself.

Also, don’t forget to filter inner exists data by RequestID.

Here is query, that should work for you:

select
    DISTINCT RequestID
from
    RequestDocumentSignature r1
where
    not exists (
        select
            1
        from
            RequestDocumentSignature r2
        where
            r2.SignatureDocumentType = 'FinalTestReport'
            and r2.RequestID = r1.RequestID
    )
    and r1.SignatureDocumentType = 'PreliminaryTestReport'
group by
    RequestID