Geek Answers Handbook

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

I need to find records in my SQL table that always appear only in a specific value.

For instance:

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 transfers and never start or stop. In this case, 000329.

+5
source share
7 answers

You can select all the tags Transmission = 'Inventory' and filter out those that 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

+2
source

You can use NOT EXISTS to exclude DeviceID, which also has a start or stop.

 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'
+7
source

You can use GROUP BY with HAVING as follows

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 want to check only Transmission in ('Starting','Stopping') , you can add Transmission in ('Starting','Stopping') instead of Transmission <> 'Inventory' in the second conditional aggregation.

+3
source

If you have only these three states with these names ( "Inventory", "Start", "Stop" ), you can use:

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

Using max here is true because "Start", "Stop" are sorted alphabetically after "Inventory" .

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

The result is the correct 000329 .

+1
source

Try it...

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

It is pretty simple:

 -- You didn't give a name for your table SELECT DeviceID from YourTable WHERE Transmission = 'Inventory'
0
source

Depending on what you want (and if you have more Transmission values)

 SELECT DeviceID, Transmission FROM yourtable WHERE Transmission NOT IN ('Starting', 'Stopping')

or

 SELECT DeviceID, Transmission FROM yourtable WHERE Transmission = 'Inventory'
0
source

More articles:


All Articles