SQL query to select rows where a column contains only specific values

user_4456809 0
Viewed 1566

I have a table identities that looks like this.

id  child   order_in_child
 1   200        34
 1   700        11
 2   200        31
 2   200        74
 3   200        35
 3   400        19

I want to select the list of ids where all child are only 200.

id  
 2
sql
2 Answers

Using aggregation, we can try:

SELECT id
FROM identities
GROUP BY id
HAVING MIN(child) = MAX(child) AND MIN(child) = 200;

The first condition of the HAVING clause asserts that a given id group of records has only a single child value. The second condition asserts that this single value is 200.

user_18632290

Try following code:

select DISTINCT id from identities where child = 200
user_164288970
Related