SQL Query To Eliminate Rows

Question

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?

Answer 1

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

Answer 2

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