There is a database containing a table with a column for a work-order number and a column for the process the work-order has to do/has done. As an example, work-order 1 might have 3 different entries, the first containing extrusion, the second entry containing sawing, and another containing shipping. Is there a way I can write a query to find all of the work-orders that are missing the sawing process? So on a table that looks like:

I would want a query that would recognize that work order 2 is missing a SAW process. I'm stuck because the only queries I can think of all involve using WHERE PROCESS != 'SAW', but that would obviously just return the other processes. Not sure if this is possible with how the table was set up, but any help is appreciated.

CodePudding user response：

One more generic method to find what is missing.

It generates a full set of the expected data, and LEFT OUT JOIN with the existing table reveals what is missing.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (Workorder INT,  Process VARCHAR(20));
INSERT INTO @tbl (Workorder, Process) VALUES
(1, 'Extrusion'),
(1, 'Saw'),
(1, 'Ship'),
(2, 'Extrusion'),
(2, 'Ship'),
(3, 'Extrusion'),
(3, 'Saw'),
(3, 'Ship');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT DISTINCT Workorder FROM @tbl
), cte AS
( 
    SELECT * 
    FROM rs
    CROSS APPLY (VALUES 
            ('Extrusion'),
            ('Saw'),
            ('Ship')) AS t(Process)
)
SELECT cte.* 
FROM cte LEFT OUTER JOIN 
    @tbl t ON t.Workorder = cte.Workorder 
    AND t.Process = cte.Process
WHERE t.Process IS NULL
ORDER BY cte.Workorder;

Output

 ----------- --------- 
| Workorder | Process |
 ----------- --------- 
|         2 | Saw     |
 ----------- --------- 

CodePudding user response：

You would want to use a not exists clause, as follows.

SELECT
     WorkOrder
FROM thetablename t
WHERE NOT EXISTS
(
     SELECT
          WorkOrder
     FROM thetablename tn
     WHERE tn.Process ='Saw'
     AND tn.WorkOrder = t.WorkOrder
)

CodePudding user response：

I think you just need to find the Workorder that is missing the full number of processes - you don't want to be hard-coding "Saw", it could be any, so just count the number of distinct processes per workorder and compare to the highest number of processes:

with w as (
    select workorder, Count(*) qty
    from t
    group by workorder
)
select workorder
from w
where qty < (select Max(qty) from w);

CodePudding user response：

Use a group by

Select workorder
From table
Group By workorder
Having count(distinct lower(process) ='saw') = 0;