CODEWITHSUNDEEP
sql
Omaja7
Omaja7

Reputation: 129

Selecting certain value from row based on another value in same row

I have a following table with following data:

Table

enter image description here

Now I want to get all those users (distinct only) who do not have value 5 in Column B. What I mean is user1 has a value 5 in some row, then all user 1 rows are dismissed.

Following result should be produced: user2 (because value is null) user3 (no value 5)

How can I do that?

Upvotes: 0

Views: 37

Answers (5)

Rajeev Pande
Rajeev Pande

Reputation: 476

One more way -

SELECT DISTINCT T1.ColumnA
FROM TableName T1
WHERE T1.ColumnA NOT IN 
(
   SELECT T2.ColumnA FROM TableName T2 WHERE T2.ColumnB = 5
)

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

Using NOT EXISTS you can able to get the result

SELECT DISTINCT T1.ColumnA
FROM TableName T1
WHERE NOT EXISTS (
   SELECT * FROM TableName T2 WHERE T2.ColumnA = T1.ColumnA AND T2.ColumnB <> 5
)

Upvotes: 1

Rhythm
Rhythm

Reputation: 682

You can do this by Minus operator

SELECT distinct colA
FROM have
WHERE colB not in(5)
MINUS
SELECT distinct colA 
FROM have
WHERE colB=5;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

One method is aggregation:

select columnA
from t
group by columnA
having sum(case when columnB = 5 then 1 else 0 end) = 0;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Perhaps the easiest way to do this would be aggregation by user:

SELECT ColumnA
FROM yourTable
GROUP BY ColumnA
HAVING COUNT(CASE WHEN ColumnB = 5 THEN 1 END) = 0;

Upvotes: 2

Related Questions