CODEWITHSUNDEEP
mysqlsqloracle-database
user3784251
user3784251

Reputation: 520

how to get distinct column value1 from a table which does not include a particular value in column value 2 when it has multiple rows with col val1?

Probably a big Title! sorry for that.. :(

Table 1

id   col1    col2

1     10     one
2     11     two
3     10     three

Now, i would like to write a sql query to get distinct col1 from table1 which doesn't have three in col2. I need the output col1 - 11 only.

I tried like this select distinct col1 from table1 where col2 != 'three' but this gives the result as both 10 and 11. But for 10 it has corresponding row with three as col2 value. Kindly help me to find this.

Upvotes: 0

Views: 47

Answers (2)

SqlZim
SqlZim

Reputation: 38063

using not in()

select distinct col1
from table1 t
where col1 not in (
  select col1
  from table1 i
  where i.col2 = 'three'
  )

or using not exists()

select distinct col1
from table1 t
where not exists (
  select 1
  from table1 i
  where i.col1 = t.col1
    and i.col2 = 'three'
  )

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use group by and having.

select col1 
from table1 
group by col 
having sum(case when col2='three' then 1 else 0 end)=0

If you are using MySQL, the having condition can be shortened to

select col1 
from table1 
group by col 
having sum(col2='three')=0

as conditions are treated as booleans returning 1 for true and 0 for false.

Upvotes: 1

Related Questions