How to get entries from SQL that only appear in certain value?

Question

I need to find entries in my SQL table that always and only appear in a certain value.

For example:

DeviceID    Transmission
--------    ------------
000329      Inventory
000980      Inventory
004406      Starting
000980      Stopping
000329      Inventory
004406      Inventory

Now I need to find all DeviceIDs that only have Inventory Transmissions and never Starting or Stopping. In this case 000329.

Answer 1

You can use NOT EXISTS to exclude DeviceID's that also have Starting or Stopping.

select DeviceID
from tablename t1
where not exists (select 1 from tablename t2
                  where t1.DeviceID = t2.DeviceID
                    and t2.Transmission in ('Starting','Stopping'))
  and t1.Transmission = 'Inventory'

Answer 2

You can use GROUP BY with HAVING like this

Query

SELECT DeviceID
FROM DevicesTable
GROUP BY DeviceID
HAVING SUM(CASE WHEN Transmission = 'Inventory' THEN 1 ELSE 0 END) > 1
AND SUM(CASE WHEN Transmission <> 'Inventory' THEN 1 ELSE 0 END) = 0

SQL Fiddle

OUTPUT

DeviceID
000329

If you only want to check against Transmission in ('Starting','Stopping'), you can add Transmission IN ('Starting','Stopping') instead of Transmission <> 'Inventory' in the second conditional aggregation.

Answer 3

You can select all Transmission = 'Inventory' ids and filter out those exist in Transmission in('Starting', 'Stopping'):

select distinct(DeviceID) from YourTable
WHERE Transmission = 'Inventory'
and DeviceID not in
( select distinct(DeviceID) from YourTable
  WHERE Transmission in('Starting', 'Stopping')
);

SQL Fiddle: http://sqlfiddle.com/#!9/81896/12

Answer 4

Try this...

select *
from tablename 
where DeviceID not in 
(select DeviceID from tablename 
where Transmission in('Starting','Stopping'))
and Transmission='Inventory';

Answer 5

Asuming you have only these three states with these names ('Inventory', 'Starting', 'Stopping') you could use:

select deviceID from table1 
group by deviceID 
having max(transmission)='Inventory'

Using max here is valid because 'Starting', 'Stopping' are alphabetically orderd after 'Inventory'.

http://sqlfiddle.com/#!9/81896/8

The result is the correct 000329.