CODEWITHSUNDEEP
sqlsubquery
Alex
Alex

Reputation: 791

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

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

Upvotes: 1

Views: 984

Answers (2)

Ergest Basha
Ergest Basha

Reputation: 8973

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 ;

Upvotes: 0

Zakaria
Zakaria

Reputation: 4806

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.

Upvotes: 1

Related Questions