Annie Jeba Annie Jeba - 1 year ago 81
SQL Question

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

Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download