I have a table like this:
| DOC | DATE | KEY | HOUR | DEPARTAMENT | STATUS |
|---|---|---|---|---|---|
| 99KN001000002 | 2000-12-28 | 12:04:51 | DEP 1 | ||
| 99KN001000002 | 2000-12-28 | 12:05:35 | DEP 2 | ||
| 99KN001000002 | 2000-12-28 | 12:06:31 | DEP 3 | ||
| 99KN001000002 | 2000-12-28 | 12:07:17 | DEP 4 | ||
| 99KN001000003 | 2000-12-29 | 12:07:17 | DEP 1 |
and I would like to find out all those documents that have "DEP 1" as the only record
>Solution :
This should work in any RDBMS:
select DOC from table_name
where DEPARTAMENT = 'DEP 1'
and DOC not in
(select DOC from table_name where DEPARTAMENT <> 'DEP 1');
You can use MINUS or EXCEPT if your RDBMS supports those.