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!

Runck

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))

Simian

I'm confident this is what I need. Of course, I'm dealing with Progress 4GL, and they make a point of giving the finger to SQL standards whenever possible, so it's giving me a fair amount of trouble. – Runck

For the record, this did solve the problem. Turns out the guy had been returning over 1,000 rows, and removing them in software to trim it down to the 80 or so he needed. This took a 24 second query down to 2 (and I'm willing to bet most of that is transactional overhead...) Thanks! – Runck

Answer 2

you could use a minus operator.

pseudo-query

select everything
from tables
where id in ( select id from table minus select id from table where val is bad )

Seducer

MINUS is Oracle specific, no? ANSI would be EXCEPT – Simian

Answer 3

you could try something like

SELECT *
FROM TABLE
WHERE TABLE.ID NOT IN(
    SELECT ID
    FROM TABLE
    WHERE TABLE.VAL < '1'
    OR TABLE.VAL > '3'
)

Florentinoflorenza

I'm not guaranteed consecutive numbers :( – Runck

well you just change the subquery from greater/less than valid range to WHERE IN (bad values) – Florentinoflorenza

Answer 4

Here is another alternative that will pass through TBL once, aggregate, and using the IDs found, retrieve the data from TBL

SELECT *
WHERE ID IN
(
    SELECT
       ID,
       CASE WHEN val in (1,2,3) THEN 1 ELSE 0 END Test
    FROM TBL
    GROUP BY ID
    HAVING MIN(val) = 1
)

For multi-column keys, and as an alternative to the above IN form, you can use the JOIN form.

SELECT T.*
FROM (
    SELECT
       Company, OrderNumber,
       CASE WHEN val in (1,2,3) THEN 1 ELSE 0 END Test
    FROM TBL
    GROUP BY Company, OrderNumber
    HAVING MIN(val) = 1
    ) KEEP
INNER JOIN TBL T ON T.Company = KEEP.Company and T.OrderNumber=KEEP.OrderNumber

Rosco

Is there a way to do it like this when ID is multi-key? For example, I have a company and order-number to identify a record. – Runck