CODEWITHSUNDEEP
sqlpostgresql
fr3ak
fr3ak

Reputation: 503

Selecting row from a table that doesn't have another similar one, not fulfilling condition

I'm sorry for the messy title, but I didn't know how to explain it without an example. Let's say I've got a table:

ID | A
-------
1  | X
1  | Y
2  | X
3  | X

I would want to select all rows that for given ID have only value X in the column A, and not Y. So in this case I would get:

ID | A
-------
2  | X
3  | X

I don't know how to construct such query. Any help would be appreciated.

Upvotes: 0

Views: 34

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do aggregation :

SELECT ID, 'X' AS A
FROM table t
GROUP BY ID
HAVING MIN(A) = MAX(A) AND MIN(A) = 'X';

If you want all columns then you can also use NOT EXISTS :

SELECT t.*
FROM table t
WHERE t.A = 'X' AND -- have only value "X" 
      NOT EXISTS (SELECT 1 FROM table t1 WHERE t1.ID = t.ID AND t1.A = 'Y');

Upvotes: 0

Vijiy
Vijiy

Reputation: 1197

Below would be one way -

select * from table where id in (select id from table group by id having count(*)=1) and id='X'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use not exists

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.a <> 'X'
                 );

Upvotes: 1

Related Questions