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.

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 — sagar, Nov 20 '15 at 16:50
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. — sagar, Nov 20 '15 at 17:03

JohnG · Accepted Answer · 2015-11-23T16:24:03.893

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

edited Nov 23 '15 at 16:24

answered Nov 20 '15 at 16:59

JohnG

1,081
1
11
26

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

score 0 · Answer 2 · answered Nov 20 '15 at 17:05

0

except

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

answered Nov 20 '15 at 17:05

paparazzo

5,043
1
18
32

score 0 · Answer 3 · answered Nov 20 '15 at 17:11

0

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...

answered Nov 20 '15 at 17:11

dnoeth

4,196
11
14

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. – sagar Nov 20 '15 at 17:37

Select data from single table using Self join

3 Answers3