求一SQL语句：子表通过一个非主键属性排除主键所在的所有行,多谢！

[hyf_0023]

一个问题：子表通过一个非主键属性排除主键所在的所有行？
比如:

create table tmp_main
(pk_field integer);
insert into tmp_main(pk_field) values(1);
insert into tmp_main(pk_field) values(2);
commit;

with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)

t_detail表里有flag_field = '1'的行，我想把满足这个条件的主键值的所有过滤掉

select b.* FROM tmp_main a,t_detail b where a.pk_field = b.pk_field and ...

想要显示的结果是：
pk_field flag_field
2 0
2 0

请热心朋友帮忙，非常感谢！

[007-x]

用not exists

with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)
select b.* FROM t_detail b where not exists (select 1 from tmp_main t where b.flag_field=t.pk_field)

[hyf_0023]

多谢，您的写法不能满足，我要求只要有一行flag_field = '1',如例，对应pk_field = 1的所有值全不要
也就是只要pk_field = 2的值。

[007-x]

不好意思，字段关联错了，修正如下：

with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)
select b.* FROM t_detail b where not exists (select 1 from tmp_main t where b.pk_field=t.pk_field)

[007-x]

没看清需求，见谅

with t_detail as(
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 1 as pk_field,'1' as flag_field from dual
union all
select 1 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
union all
select 2 as pk_field,'0' as flag_field from dual
)
select b.* FROM t_detail b where not exists (select 1 from t_detail t,tmp_main t2 where  b.pk_field=t.pk_field and t2.pk_field=t.flag_field)

[hyf_0023]

我想了个很麻烦的方法
select b.* FROM tmp_main a,
(select pk_field,flag_field FROM (select pk_field,flag_field,count(*) over() as c1,count(*) over(order by pk_field,flag_field) as c2 from t_detail) t where t.c1=t.c2) b
where a.pk_field = b.pk_field

非常感谢，还是您的方法简单。结帖。