sql insert value from another table with original nulls but not unmatched entries -

ok. hard 1 explain, replacing type of foreign key in database. need update values in table references it. fine , good, , nice , easy do.

i'm inserting stuff temporary table replace original table, insert query isn't @ difficult, it's select values from.

however, want keep entries original reference null. not hard, use left inner join that.
we're not done yet: don't want entries there no match in second table. i've been dinking around 2 hours now, , no closer figuring out moon.

let me give example data set:

____________________________ | inventory  ||  customer  | |============||============| | id   cust  || id    name | |------------||------------| |  1       ||  1       | |  2     b   ||  2     b   | |  3     e   ||  3     c   | |  4   null  ||  4     d   | |____________||____________| 

let's database used use customer.name field primary key, , need change standard int identity(1,1) not null id. i've added field no issues in customer table, , kept name because need other stuff. have had no trouble in tables not allow nulls, since "inventory" table allows associated no customer, i'm running troubles.
if did left inner join, results be:

______________ |   results  | |============| | id   cust  | |------------| |  1     1   | |  2     2   | |  3   null  | |  4   null  | |____________| 

however, inventory #3 referencing customer not exist. want filtered out. database development database, hack, slash, , destroy things wanton disregard validity. lot of links in these tables no longer valid. next step replicating process in beta-testing environment, bad records shouldn't exist, can't guarantee that. i'd keep filter, if possible.

the query have right using sub-query find rows in inventory custid either exists in customers, or is null. tries grab value rows subquery found. here's translated query:

insert results (     id,     cust ) select     inv.id, cust.id inventory inv, customer cust inv.id in      (         select inv.id inventory inv, customer cust         inv.cust null         or cust.name = inv.cust     ) , cust.name = inv.cust  

but, i'm sure can see, query isn't right. i've tried using 2, 3 subqueries, inner joins, left joins, bleh. results of query, , many others i've tried (that weren't horribly, horribly wrong) are:

______________ |   results  | |============| | id   cust  | |------------| |  1     1   | |  2     2   | |____________| 

which inner-join. considering actual data has around 1100 records have null values in field, don't think truncating them answer.

the answer i'm looking is:

______________ |   results  | |============| | id   cust  | |------------| |  1     1   | |  2     2   | |  4   null  | |____________| 

the trickiest part of insert select fact i'm looking insert either value table, or value table or literal null. isn't know how do; i'm still getting hang of sql.

since i'm inserting results of query table, i've considered doing insert using select leaves out null values , un-matched records, going through , adding in null records, want learn how more advanced queries this.

so of yous folks have ideas? 'cause i'm lost.

how union?

select records id , cust match , union records id matches , inventory.cust null.