I'm on Db2 for IBM I. Except this might also apply to SQL Server.

Here's an example of what the data looks like in my HISTRY table...

I want to select all rows with STATUS = HM, but not if a row has a corresponding row with STATUS = AM, which TICKETNUM ab123456 has. I tried using WHERE NOT IN (..) but that doesn't address the problem. My other thought was that I might have to use a temp table which I'm just slightly leery of because of the cleanup involved afterward.

Currently, in order to get what I want, I'm using C# code where I'm creating two List with one list containing all HM statuses and the other containing all AM, then For-Each looping with a sub For-Each removing any AMs from the HM's list. Brutish but it works. Now am backtracking because I would prefer the logic for this special needs SELECT to be handled all by SQL in a stored procedure.

Any input and extra brain muscle and elbow grease would be much appreciated. TIA!

CodePudding user response：

Typically you would use a left join or the NOT EXISTS operator.

A solution with the latter can look like:

select *
from HISTRY a
where status = 'HM'
  and not exists (
    select 1
    from HISTRY b 
    where b.ticketnum = a.ticketnum and b.status = 'AM'
  )