Select data from single table using Self join

Question

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.

• sagar: Yes i tried but didnt post it because i thought i am way off. Appreciate your reponse but i posted it after trying. select a.id from a.tableone a, (select c.id, count(1)from tableone c group by c.id having count(1) > 1)b where a.id = b.id and a.id = 1 and a.id <> 8
• paparazzo: Why does it have to be a self join?
• sagar: As i need to match data within same table and depending on it group. I may be wrong. If you suggest any approach i can try myself.

Answer 1

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

• sagar: Hi John, Thank you for the help it worked. I have miles to go in SQL queries.
• JohnG: take it one step at a time. Lots of research and patience ;-)
• jkavalik: You should not even need the subquery.
• JohnG: @jkavalik you're right... fixed the query.

Answer 2

except

select id from from table where value = 1 
except 
select id from from table where value = 8

Answer 3

You might try a conditional aggregate:

select id
from table
where value in (1,8) -- 1 or 8
group by id
having sum(case when value = 8 then 1 else 0 end) = 0 -- only 1, no 8

or a NOT EXISTS:

select id
from table as t1
where value = 1 -- 1
and not exists -- but no 8
 ( select * 
 from table as t2
 where t1.id = t2.id
 and t2.value = 8 
 )

Which on is more efficient depends on your actual data and existing indexes...

• sagar: Hi Thank for the response. I have soo many different queries to address my issue. This forum is awesum. I have just started writing SQL queries and all this input will help me.