Select values for a column only if there are no rows with that value that satisfy a condition

I have a table that stores logs of http fetches, let’s say we have two columns: fetch_url and success. A few example rows:

success   fetch_url
TRUE      A
FALSE     A
FALSE     B
FALSE     B
FALSE     C

As you can see, there is at least one fetch for A that succeeded, but not a single fetch succeeded for B or for C. How can I write a query that will select only those fetch urls that have never succeeded? i.e.:

fetch_url
B
C

I’m trying to use some combination of GROUP BY, COUNT, and HAVING, but I can’t get a correct query working.

Answer

You can use aggregation:

select fetch_url
from t
group by fetch_url
having sum(case when sucess = 'TRUE' then 1 else 0 end) = 0;

Advertisement

Answer