Finally, i found the query.

Thanks Ashish. Great support.

below is the query:

SELECT a.agentid,a.actid,c.restid,c.plancode

from a1 a, c1 c

where c.restid not in (select restid from b1) or a.actid not in (select...

Ashish,

Thanks. it works. But if add another record in table A1, it does not work as expected:

select * from A1;

gives:

agentid, actid

1, 100

2, 200

In this case,

agent 1 have plancode AC,

but...