SQL select rows that have one specific value but not another in the same table-CodePudding

I have a table like this:

DOC	DATE	HOUR	DEPARTAMENT
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

CodePudding user response：

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.

CodePudding user response：

Another way:

select t.* 
from  test_tbl t 
inner join (select doc, 
            count(distinct STATUS) as nr_status
            from test_tbl 
            group by doc
            ) as t1 on t.doc=t1.doc
where t1.nr_status =1 ;