1

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
    )

1 Answer 1

0

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
)
1
  • If i do SELECT * instead of SELECT DISTINCT io.process_name, I still get this row: VSC|VSC_IN1|VSC_RESULT. In my particular case 'process_io_table' is actual a temporary structure 'R' using a WITH R AS(...) statement, that wouldn't have an effect would it? So when I do the SELECT DISTINCT io.process_name, I am still getting AVA and VSC
    – codeNoob
    Jul 18, 2022 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.