SQL query to filter data by a specific column value with no deduplication

Question

I have a table in a clickhouse database that keeps track of items and their statuses. There can be multiple records with the same name. Each time a record is ingested it is given a status of either 0 or 1. A simplified version of the data as follows:

name	status
Obi	0
Obi	0
Harold	0
Harold	1
Ann	0
Ann	1
Lily	0
Lily	0
Lily	0
Ron	0
Ron	1

I only want to display the records that have never been given a status of 1, with no de-duplication. The values returned should be:

name	status
Obi	0
Obi	0
Lily	0
Lily	0
Lily	0

What should the query look like in order to accomplish this?

I have tried something like this, but it returns every record that has ever had a status of 0.

SELECT t.*
FROM example_table t
INNER JOIN
(
    SELECT Name
    FROM example_table
    GROUP BY Name
) t2 ON t.Name = t2.Name
WHERE t.findingStatus=0

I have tried variations of the above query but have not had any luck. I was thinking maybe an inner join where the left table has status=0 and the right table where status has never been 0. Not sure how to get that to work. Any help on this is much appreciated. Thanks!

Answer 1

Try this query:

SELECT *
FROM example_table
WHERE name NOT IN 
(
    SELECT name
    FROM example_table
    GROUP BY name
    HAVING countIf(status != 0) > 0
)