Exclude Records based on values from a different column

Question

I have a table that looks like this:

| CODE   |  LEVEL1  |  LEVEL2  |  LEVEL3  |   SEC_ID   |
| CODE1  |  LEV1    |  LEV2    |  LEV3    |   123456   |
| CODE2  |  LEV1    |  LEV2    |  LEV3    |   234561   |
| CODE2  |  LEV1    |  LEV2    |  Term    |   345612   |
| CODE3  |  LEV1    |  LEV2    |  LEV3    |   456123   |

I need my query to look for the word "Term" in the Level3 column and then exclude every row that has the same CODE.

So in the above example the query would exclude rows 2 and 3 from the result.

Let me know if this is not clear.

Answer 1

You can use NOT EXISTS to exclude any records with the level3 value and the same code:

select *
from yourtable t1
where not exists (select *
                  from yourtable t2
                  where level3 = 'Term'
                   and t1.code = t2.code)

See SQL Fiddle with Demo

Result:

|  CODE | LEVEL1 | LEVEL2 | LEVEL3 | SEC_ID |
---------------------------------------------
| CODE1 |   LEV1 |   LEV2 |   LEV3 | 123456 |
| CODE3 |   LEV1 |   LEV2 |   LEV3 | 456123 |