Select a row that matches the condition if no other rows match another condition

Question

Very simple, but I don’t know how 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 inactive names only if not one with this name is active)

This will be used to allow users to activate inactive elements, but I want to query db to make sure the element is not active yet.

Answer 1

You are looking for a template DOES NOT EXIST.

"For every name that is not active, there are no rows 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)