CODEWITHSUNDEEP
sqloracle-databaseselect
aim_thebest
aim_thebest

Reputation: 95

Oracle selecting record with multiple column condition

Here i want to select the enames for which the date is null for both the events 37 and 49 AND also the enames for which this two events(37,49) are not linked. If date for any event is not null then it shouldn't select that ename. Here in this example it should fetch only C1,D1,E1 ename. D1 and E1 because does not link to event no 37 and 49

Can anyone please help me out with this.

id  ename   event   date
1   A1      37      1-Oct-16
2   A1      49      NULL
3   C1      37      NULL
4   C1      49      NULL
5   D1      50      NULL    
6   E1      30      NULL

Just added one more condition. Please

Upvotes: 0

Views: 89

Answers (3)

Matt
Matt

Reputation: 14381

SELECT
    ename
FROM
    TableName
GROUP BY
    ename
HAVING
    COUNT(CASE WHEN event IN (37,49) AND date IS NOT NULL THEN 1 END) = 0

The NOT EXISTS in @Serg's answer is a great way to go but you can also use conditional aggregation to do it without a sub query. When using an aggregation formula such as COUNT() or SUM() they ignore Null values so if the ELSE portion of your CASE expression is NULL it will get ignored and by default ELSE is NULL if it is not defined.

Upvotes: 0

Pritam Parashuram Thakur
Pritam Parashuram Thakur

Reputation: 133

Changing query assuming that your data does not contains duplicate rows:

 SELECT * 
 FROM   table_name 
 WHERE  event IN ( 37, 49 ) 
   AND ename NOT IN(SELECT DISTINCT( ename ) 
                    FROM   table_name 
                    WHERE  date IS NOT NULL)

 UNION
 SELECT * 
 FROM   table_name 
 WHERE  event NOT IN ( 37, 49 ) 
   AND ename NOT IN(SELECT DISTINCT( ename ) 
                    FROM   table_name 
                    WHERE  date IS NOT NULL);

Upvotes: 0

Serg
Serg

Reputation: 22811

Count relevant rows. If only ename is needed

select ename
from mytable
where event in (37,49) and date is null
group by ename
having count(*) = 2

EDIT

Following new set of conditions

select distinct ename
from mytable t1
where not exists ( 
    select 1 
    from mytable t2
    where t2.ename = t1.ename and t2.event in (37,49) and t2.date is not NULL)
    ;

Upvotes: 1

Related Questions