SQL Query: if a row contains a specific value, ignore all rows that have the same data identifier

All we need is an easy explanation of the problem, so here it is.

I have this table process_io_table:

process_name input output
AVA AVA_IN1 AVA_RESULT
AVA AVA_IN2 AVA_RESULT
VSC VSC_IN1 VSC_RESULT
VSC AVA_RESULT VSC_RESULT

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 (AVA_RESULT).

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply