CODEWITHSUNDEEP
sqlintersect
nametal
nametal

Reputation: 1501

Intersect table with different column value

I have a table:

num type flag
--- ---- ----
11   A    1
12   A    1
13   A    1
14   A    1
15   A    1

12   B    2
13   B    2

How would I write a query to get the following result:

num type flag
--- ---- ----
11   A     1
14   A     1
15   A     1

Upvotes: 1

Views: 391

Answers (2)

juergen d
juergen d

Reputation: 204746

select num
from your_table
where num not in
(
  select num
  from your_table
  where type = 'B'
)

Upvotes: 2

Robert
Robert

Reputation: 25753

Try to use not exists as below

select * 
from tab  t
where t.type = 'A'
and not exists 
(
  select 1
  from tab t1
  where t1.type = 'B'
  and t1.num=t.num

)

Upvotes: 0

Related Questions