Find what values are not in multiple columns

Question

I need to find what "Codes" from column "RMS_CODE" and column "RMS_ALT1_Cde" are not being used in the Child # (starting in position 6 for a length of 4) column within the "APC_Spec" number. I tried using the sql in the image provided, but it only looks in the row and doesn't check the whole column within the "APC_Spec" requirement. Can anyone suggest a method to check which codes are not being used per "APC_Spec" in the Child column #?

score 1 · Accepted Answer · edited Aug 05 '20 at 19:51

1

Try this:

with tRMS_CODE as 
   (SELECT DISTINCT RMS_CODE FROM RMS_Table)
   , tRMS_ALT1_Cde as 
   (SELECT DISTINCT RMS_ALT1_Cde FROM RMS_Table)
SELECT DISTINCT * FROM RMS_Table
WHERE 
   SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_CODE) AND 
   SUBSTR(CHILD,6,4) NOT IN (SELECT * FROM tRMS_ALT1_Cde) AND 
   APC_Spec='36559'

edited Aug 05 '20 at 19:51

Lennart - Slava Ukraini

23,842
3
34
72

answered Apr 30 '20 at 16:53

Tomy

36
2

Find what values are not in multiple columns

1 Answers1