How to find missing alpha values in sets of data within same table in SQL

Question

I have a table of many values where one column has the WO Number, and another column has the Resource ID. I need to be able to find all the WO numbers that do not have a resource value of "RW".

Here is an example of the typical information. I need to be able to know that work order 5678 does not have an "RW" Resource ID.

WO Number - Resource ID
1234 - IN
1234 - WE
1234 - AS
1234 - RW
5678 - PR
5678 - WE
5678 - IN
5678 - AS

Answer 1

SELECT DISTINCT M1.WONumber
FROM Mytable M1
WHERE
    NOT EXISTS (SELECT 1/0 --or NULL, 1, *, 'bob'. All the same
                  FROM Mytable M2
                  WHERE M2.ResourceID = 'RW' AND M1.WONumber = M2.WONumber)

Answer 2

IF you know the number of possible resources and each WO Number can have at most 1 of each possible resource, then simply search for those WO Numbers that don't have all possible resources:

SELECT WO_Number 
FROM <table> 
GROUP BY WO_Number 
HAVING COUNT(*) <> (number of resources)

Answer 3

Subtract the WO numbers with 'RW' from the set of all WO numbers:

SELECT DISTINCT wo_number FROM mytable
MINUS
SELECT DISTINCT wo_number FROM mytable WHERE resource_id = 'RW'

Should work. I'm not an SQL power-user however, so your mileage may vary.