CODEWITHSUNDEEP
sqloracle-databasenot-exists
Rahul
Rahul

Reputation: 3

Filter data based on condition

I have a table like below. I need to find out the employes who have rank R1 but never have rank C1 and C2. 

Id  ECode Name Rank
1   EMP1    AA  R1
2   EMP2    BB  R1
3   EMP1    AA  R2
4   EMP1    AA  C1
5   EMP1    AA  C2
6   EMP1    AA  C3
7   EMP2    BB  C4
8   EMP2    BB  C5
9   EMP3    CC  R1
10  EMP3    CC  C1
11  EMP3    CC  C2
12  EMP3    CC  C4
13  EMP4    DD  R1
14  EMP4    DD  C3

Upvotes: 0

Views: 86

Answers (6)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Evidently use NOT EXISTS :

select *
  from mytable t
 where t.rank = 'R1' 
   and not exists ( select ECode from mytable where ECode = t.ecode and  rank in ('C1','C2') );

Upvotes: 0

schikkamksu
schikkamksu

Reputation: 85

Select * from (tablename)
where Rank = 'R1'
and Rank not in (Select Rank from (tablename)
where Rank = 'C1' 
or Rank = 'C2')

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I would do:

SELECT ecode, name
FROM t
WHERE rank IN ('R1', 'C1', 'C2')
GROUP BY ecode, name
HAVING MIN(rank) = MAX(rank) AND MAX(rank) = 'R1';

Upvotes: 1

Yogesh.Sharma
Yogesh.Sharma

Reputation: 1

You can use combination of exists and not exists:

select * 
from table t 
where exists (select 1 from table where ECode = t.ECode and Rank = 'R1') AND 
      not exists (select 1 from table where ECode = t.ECode and Rank IN ('C1', 'C2'))

Upvotes: 0

Fabricio Ardizon
Fabricio Ardizon

Reputation: 106

Try this:

SELECT *
  FROM EMPLOYES  A
 WHERE RANK = 'R1'
   AND NOT EXISTS(SELECT 1 
                    FROM EMPLOYES  B
                   WHERE B.ECODE = A.ECODE
                     AND RANK IN ('C1','C2')
                     AND ROWNUM = 1)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

One approach uses aggregation by employee:

SELECT ECode, Name
FROM yourTable
GROUP BY ECode, Name
HAVING
    SUM(CASE WHEN Rank = 'R1' THEN 1 ELSE 0 END) > 0 AND
    SUM(CASE WHEN Rank IN ('C1', 'C2') THEN 1 ELSE 0 END) = 0

Upvotes: 1

Related Questions