CODEWITHSUNDEEP
sql
Jack Mills
Jack Mills

Reputation: 6132

SQL Query To Eliminate Rows

I have a table of data, that looks something like this:

ID       CODE

1        FOO
1        FOO2
1        FOO3
1        BADCODE
2        FOO
2        FOO2

When I perform a query on this table I essentially want to discount all rows that contain the same ID if a bad code is found. So in the example above nothing with an ID of 1 would be returned as a badcode has been found in one of the rows.

Sorry if this hasn't been explained in the most eloquent way. Any ideas?

Upvotes: 2

Views: 122

Answers (2)

GvS
GvS

Reputation: 52518

SELECT * FROM mytable WHERE id NOT IN (SELECT id FROM mytable WHERE code = 'BADCODE')

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425341

SELECT  *
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi
        WHERE   mi.id = mo.id
                AND mi.code = 'BADCODE'
        )

Upvotes: 3

Related Questions