CODEWITHSUNDEEP
sqlsql-server
TEC C
TEC C

Reputation: 153

SQL Show groups where record is missing

I have two columns that I am looking at one for job order and one for operation number for each job order there is a series of operations 1-4 so it would look something like.

Job Order       Operation 
------------------------
00023            1
00023            2
00023            3 
00023            4
00024            1
00024            2
00024            3 
00024            4
00025            1
00025            2
00025            3 
00025            4
00026            2
00026            3 
00026            4

I would like to show all Job orders that are missing Operation 1. In this example since they would be grouped I would only see one instance of job order 00026 since that is the only job order missing operation 1. The desired out put Would look like

Job Order
---------------
00026

Upvotes: 1

Views: 58

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

There are several solutions for this:

select distinct job_order from table
where job_order not in (select job_order 
                        from table
                        where operation = 1)

Another one is:

select distinct t1.job_order from table t1
where not exists (select *
                  from table t2
                  where t2.operation = 1 and t2.job_order = t1.job_order)

Next one could be:

select distinct job_order 
from table
except
select job_order 
join table
where operation = 1

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42753

select Job_Order 
from your_table
group by Job_Order 
having min(Operation) > 1

Upvotes: 3

Related Questions