>I am using the following select statement to select data from one table that doesn't exist in another table. I need to modify it so it selects only data that doesn't appear in the first table or in another table. The select statement currently returns 1303 records. I tried modifying the WHERE clause to read: WHERE (a.ssno NOT IN (SELECT b.ssno FROM helth_priors_tmp b) AND (a.ssno NOT IN (SELECT b.ssno FROM helth_currents_tmp b)
>
>But that returns 29909 records! Any ideas?
>
>SELECT a.dedtype, mnpd, name, prsnid, unit, div, dept, subdept,;
>eeclascd, prioradj, a.ssno, a.sortkey, famsingl, frfamsng, dedamnt,;
>termcode, paidthru, addcode, adjamt, adjmonth, efctdate,;
>a.user_comment;
>FROM helthins_view a;
>WHERE a.ssno NOT IN (SELECT b.ssno ;
>FROM helth_priors_tmp b);
>INTO cursor cHelth_tmp3
>
>
>Jeff

Keep SQL simpler:
SELECT ssno ;
FROM helth_priors_tmp ;
INTO cursor tmp1 ;
UNION SELECT ssno FROM helth_currents_tmp

SELECT dedtype, mnpd, name, prsnid, unit, div, dept, subdept,;
eeclascd, prioradj, ssno, sortkey, famsingl, frfamsng, dedamnt,;
termcode, paidthru, addcode, adjamt, adjmonth, efctdate,;
user_comment;
FROM helthins_view ;
INTO cursor cHelth_tmp3 ;
WHERE ssno NOT IN (SELECT ssno FROM tmp1)