Select data from single table using Self join I have requirement to select a data from single table. The requirement is as follows
Select data from table which has one value but not another.
for e.g
I want to select distinct ID which has value 1 but not value 8 present. For for above e.g the desired o/p should be

ID      Value
123     1
123     8
234     8
456     1
876     5
876     1
765     8
765     5

O/p
ID

456
876


Also my table contains 500K record so query should be able to execute with good performance
Any help regarding it as i am stuck.
Here's how i got it to work: 

create table tableone (id int, value int)
insert into tableone values (123,     1);
insert into tableone values (123,     8);
insert into tableone values (234,     8);
insert into tableone values (456,     1);
insert into tableone values (876,     5);
insert into tableone values (876,     1);
insert into tableone values (765,     8);
insert into tableone values (765,     5);
Select a.*
from tableone a
left join tableone X on a.id = x.id and x.value = 8
Where a.value = 1
and X.id is null
drop table tableone