CODEWITHSUNDEEP
sql
NewbieUser
NewbieUser

Reputation: 173

Get values which exist in one record but doesn't exist in another

I have these table:

| NUMBER | NUMBER_LIST |     
| 520    | 1           |    
| 430    | 1           |    
| 870    | 1           |    
| 430    | 2           |

And I would like to get all NUMBERs which are exist in NUMBER_LIST = 1, but I wouldn't to get NUMBERs which are exist in NUMBER_LIST = 2. Finally I should get numbers 870 and 520 but I shouldn't get number 430 because this number exist in NUMBER_LIST = 2.

How I can do it?

Upvotes: 0

Views: 33

Answers (2)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

try this

select number from table1 group by number having count(number) = 1

for MSSQL

result: 

| number |
|--------|
|    520 |
|    870 |

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

Here is a simple way to do that:

select NUMBER from TABLE
where NUMBER_LIST = 1
and NUMBER not in (select NUMBER from TABLE where NUMBER_LIST=2)

Or you could use a self-join instead of a subquery:

select NUMBER 
from TABLE t1
left join TABLE t2 on t1.NUMBER=t2.NUMBER and t2.NUMBER_LIST=2
where t1.NUMBER_LIST = 1
and t2.NUMBER is null

Upvotes: 2

Related Questions