Annie Jeba - 8 months ago 49

SQL Question

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

Source (Stackoverflow)