Insert Question

[Phillip Nelson <phillip(dot)nelson(at)ints(dot)com>]

I have an ivfm_tmp table and am trying to insert all records from that 
table into inventory_data if they do not already exist. Furthermore, I 
need to join commissions from ivfmx_tmp table if there are existing 
records for that id.

This insert statement works fine as long as their is an associated 
ivfmx_tmp record to join. If there is no ivfmx_tmp record to join the 
record is not inserted and there doesn't seem to be any errors. 
Ultimately I need to default the ivfmx_tmp.comm1, comm2, and comm3 
fields to 0.00 if there is no join found and insert the record into 
inventory_data. Is this possible?

INSERT into inventory_data select ivfm_tmp.id, ivfm_tmp.cat, 
ivfm_tmp.desc, ivfm_tmp.seq, ivfm_tmp.type, ivfm_tmp.bcode1, 
ivfmx_tmp.comm1, ivfmx_tmp.comm2, ivfmx_tmp.comm3
       from ivfm_tmp INNER JOIN ivfmx_tmp
         ON ivfm_tmp.id = ivfmx_tmp.id where ivfm_tmp.id not in
           (select inventory_data.id from inventory_data);