progress db - Exclude rows based on other rows (SQL)

Question

Say I have a query like this:

SELECT *
FROM TABLE

And it returns this:

TABLE
ID | DATA | VAL
===============
01 | ABCD | 1
01 | DEFG | 2
02 | FGHI | 3
02 | HIJK | 2
03 | JKLM | 3
03 | LMNO | 4
04 | NOPQ | 0
04 | PQRS | 1

Currently I have a query that attempts find only good values like this, but it's flawed because IDs that have bad VALs in other rows are included, which is not what I'd like.

SELECT *
FROM TABLE
WHERE TABLE.VAL IN ("1","2","3")

would return this (with LMNO and PQRS missing):

TABLE
ID | DATA | VAL
===============
01 | ABCD | 1
01 | DEFG | 2
02 | FGHI | 3
02 | HIJK | 2
03 | JKLM | 3
04 | NOPQ | 0

However, I only want rows where the ID has NO bad values. So, 01 and 02 are fine because all of their rows have good results. 03 and 04 are bad because they're tainted by the bad results in other rows.

I could just bring the result in like this and process it that way in software, but it seems as though this should be possible with a database, and as a general rule, doing it on the database is better than in software (you know, that's kind of what they're there for...)

The best I could come up with is this:

SELECT *
FROM TABLE
WHERE COUNT( SELECT ID
             FROM TABLE
             WHERE TABLE.VAL NOT IN ("1","2","3")
           ) = 0

Is this viable? Is there a better alternative?

Thanks!

Answer 1

Use:

SELECT * 
  FROM TABLE a
 WHERE a.val IN (1,2,3)
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE b
                   WHERE b.id = a.id
                     AND b.val NOT IN (1, 2, 3))