Select row matching a condition if no other rows match a different condition

Question

Very basic, but didn't know what this type of query is called...I want a query that will basically do this (pseudo-SQL):

SELECT name 
  FROM Table  
 WHERE activity_status != 'active'  
   AND there are no rows with that same name where activity_status = 'active'  

(in other words, return names of the inactive only when not one with that name is active)

This will be used to allow users an option to reactivate inactive items, but I want to query the db to make sure that item isn't already active.

Answer 1

You're looking for a NOT EXISTS pattern.

"For each name that is not active, no rows exist for the same name that are active"

SELECT name FROM Table T1
WHERE activity_status != 'active'
AND
NOT EXISTS (SELECT *
        FROM Table T2
        WHERE
            T2.activity_status = 'active' AND T1.name = T2.name)