CODEWITHSUNDEEP
sqlsql-server
futurenext110
futurenext110

Reputation: 2109

How to get records from one column such that there is no association with value in another column

I have the following MSSQL table:

Col1 Col2
A     x
A     y
A     z
B     x
B     y
C     x
C     z

I want all the values from Col1 such that they have no record of association with a particular value of Col2

For example, I want value from Col1 such that 'z' does not occur for that value. The answer should be B

Upvotes: 2

Views: 93

Answers (5)

Oto Shavadze
Oto Shavadze

Reputation: 42763

One another way:

select Col1 
from your_table
group by Col1
having sum( case when Col2 = 'z' then 1 else 0 end ) = 0

Upvotes: 3

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS I think you want to retrieve the value where the given value does not exist in both the columns:

create table #sample(Col1 char(1), Col2 char(1))
insert into #sample values
('A',     'x'),
('A',     'y'),
('A',     'z'),
('B',     'x'),
('B',     'y'),
('C',     'x'),
('C',     'z')

declare @search char(1) = 'z'

select distinct col1 
from #sample
where col1 not in (
select distinct Col1
from #sample
where (col1 = @search or col2 = @search))

OUTPUT:

col1
B

Upvotes: 0

Zedee.Chen
Zedee.Chen

Reputation: 212

select distinct col1 
     from [table] 
  where col1 not in (
                  select col1 from [table] where col2 = 'z'
                     );

Upvotes: 0

AakashM
AakashM

Reputation: 63338

There are a number of ways to do this. For me the clearest is to use EXCEPT:

SELECT Col1 FROM MyTable
EXCEPT
SELECT Col1 FROM MyTable WHERE Col2 = 'z';

Here we are clearly and simply saying that we want all the Col1 values, except those Col1 values that have a z entry. EXCEPT will automatically de-duplicate the result.

Upvotes: 0

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

You can make use of the lesser known EXCEPT keyword, like this :

SELECT Col1 FROM TableName
EXCEPT
SELECT Col1 FROM TableName WHERE col2 = 'z'

You can see this here -> http://rextester.com/KPZMB79095

Hope this helps!!!

Upvotes: 2

Related Questions