CODEWITHSUNDEEP
sqloracle-database
Annie Jeba
Annie Jeba

Reputation: 379

Fetch results that do not have specific values

Sample data (Oracle DB)

HID   Result    ResultDate
6150  Interim    23-03-1990
6150  FINAL      24-03-1990
6150  Interim    25-05-1990
6180  Interim    30-08-2016
6190  Interim    31-08-2016

I have been asked to find all the HID's which do not have Final Results.

And the expected output is 

    HID   Result    ResultDate
    6180  Interim    30-08-2016
    6190  Interim    31-08-2016

Upvotes: 1

Views: 35

Answers (1)

sgeddes
sgeddes

Reputation: 62841

Here's one option count with case:

select hid
from yourtable
group by hid
having count(case when result = 'FINAL' then 1 end) > 0

This returns 6180 and 6190 since they don't have any corresponding rows with result = 'final'.

Given your edits, here's an option using not exists:

select * 
from yourtable y
where not exists (
    select 1
    from yourtable y2
    where y.hid = y2.hid and y2.result = 'FINAL'
)

Upvotes: 2

Related Questions