Help needed with a query

I have some data that looks like this:

field1(pk) field2 field3

563136 199535840A D2119
562408 199615461C D2119
562806 199748610A D2119
547463 199748610A D2368
562278 200255598A D2368
562286 200255598A D2468

Field2 can have the same value.

What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'.

So, I can't simply say:

SELECT a.field2
FROM table1 a
inner join table2 b on a.field2 = b.field2
and a.field5 = b.field5
where a.field3 not like 'D2468'

because it will still return record 5.

Can anyone help me with this? Thank you!

SELECT *

FROM table1

WHERE field2 not in (SELECT field2 from table1 WHERE field3='d2468')

|||

If you do not want records with duplicate field2 values then records 3 and 4 should also not be included in the result list since they have same values for field2 right?

So you would only need the first 2 rows. for 3,4 rows and for 5,6 rows the field2 value is same. If that is the case your query would look like:

SELECT *

FROM

table1 t1

WHERE

t1.field2 NOT IN ( SELECT field2

FROM table1

GROUP BY field2

HAVING COUNT(*) > 1)

AND t1.field3 <> 'D2468'

|||

I'm not 100% sure what you require, but I think this would also work:

SELECT Field1, Field2, Field3FROM Table1

WHERE Field2NOTIN(SELECT field2FROM Table1WHERE Field3='D2468'GROUPBY Field2)

ORDERBY Field3