All we need is an easy explanation of the problem, so here it is.
I have this table
I would like to get the distinct list of process_names that do not have a row that has an input value found in the output column. In this example, I would expect to return
process_name ‘AVA’ because AVA input rows do not contain a value from the output column. VSC on the other hand would not be included because it has a row with an input found in the output column (
I tried something along the lines of
SELECT DISTINCT io.process_name FROM process_io_table io WHERE io.input NOT IN (SELECT io.output FROM process_io_table io)
However, this still returns both AVA and VSC because VSC has a row with an input not in the output table (VSC_IN1).
How do I get process_names that don’t have ANY rows with input values found in the output column?
UPDATE: I think with the help of the person who answered I have it figured out:
SELECT DISTINCT R.process_name FROM process_io_table as R WHERE R.process_name NOT IN( -- Gets process_names NOT IN the list of process_names I don't want SELECT DISTINCT t1.process_name FROM R t1 WHERE EXISTS ( -- finds the process_names I DON'T want SELECT 1 FROM R t2 WHERE t1.input= t2.output )
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You’re almost there.
SELECT DISTINCT io.process_name FROM process_io_table io1 WHERE NOT EXISTS ( SELECT 1 FROM process_io_table io2 WHERE io1.input = io2.output )
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂