Exclude entries based on values ​​from another 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 search for the word "Term" in the Level3 column, and then exclude each row with 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 entries with a value of level3 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 |