CODEWITHSUNDEEP
sqlduplicates
fordo19
fordo19

Reputation: 51

How to SQL Query records from Multiple that Equal 0?

I have a table that has multiple duplicate records in the first column (ID records), but has varying numerical data in the second column.

I want to be able to identify which ID records have 0 for all of their numerical records.

For example the table can look like:

ID Value

1  2

1  2

1  0

2  0

2  0  

2  0

I would want to only identify ID 2 because all the values are equal to 0. I don't want ID 1 because there are values > 0

Sorry if this isn't formatted properly or confusing.

Upvotes: 0

Views: 74

Answers (3)

Joachim Sauer
Joachim Sauer

Reputation: 505

You might use "NOT IN":

SELECT DISTINCT Id
FROM table1
WHERE Id NOT IN (SELECT Id FROM table1 WHERE Value <> 0)

Upvotes: 1

Hogan
Hogan

Reputation: 70513

SELECT DISTINCT ID FROM TABLE 
WHERE ID NOT IN (SELECT DISTINCT ID FROM TABLE WHERE VALUE <> 0)

This will take all ID in the table where there is not a row where the value is non-zero.

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32145

SELECT *
FROM table t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.ID = t1.id AND Value <> 0)

"Select all records whose ID is not in the set of records that have a non-zero Value."

Upvotes: 0

Related Questions