SQL return results that excludes a value category and also its parent value

Transact-SQL
1

Consider this code:

Select
HelpCall.CallID,
Instruction.InstructionID
From HelpCall
Left join Instruction on HelpCall.HelpCallKey = Instruction.LinkKey

This produces a list of Help Calls and Instructions. Instructions can only be generated from a Help Call, each Help Call can have many Instructions:

CallID InstructionID
SD137710 RFW062182
SD137710 DMI000022
SD137713 RFW062184
SD137705 DMI000072
SD137706 RFW062180

I want to query only where is a Help Call that contains an InstructionID beginning with 'RFW%' but does not contain an InstructionID that begins with 'DMI%'.
When I execute the below code to exclude DMIs (for the purpose of illustrating my issue), it obviously still returns HelpCallID SD137710. I don't want it to, because that Help Call contains an InstructionID beginning with DMI which is fine for this issue:

Select
HelpCall.CallID,
Instruction.InstructionID
From HelpCall
Left join Instruction on HelpCall.HelpCallKey = Instruction.LinkKey and Instruction.InstructionID not like 'DMI%'

CallID InstructionID
SD137710 RFW062182
SD137713 RFW062184
SD137705 DMI000072
SD137706 RFW062180

Using the above results as an explanation, I want the query only to return SD137713 and SD137706 and its corresponding Help Call ID, because those Help Calls only contain an InstructionID beginning with RFW.

Thanks in advance.

2

See if this does what you need:


Select
HC.CallID,
I.InstructionID
From HelpCall HC
inner join Instruction I on HC.HelpCallKey = I.LinkKey and 
    I.InstructionID LIKE 'RFW%' and 
    not exists(select * from Instruction I2 where HC.HelpCallKey = I2.LinkKey and 
        I2.InstructionID LIKE 'DMI%')
3

This is brilliant, thanks Scott!