Annie Jeba Annie Jeba - 1 month ago 9
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

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'
)
Comments