(T-Sql)-Nulls on one side but not the other 1

[markajem]

Sorry about the title of the thread. Could not think of a better one to describe the problem.

My main objective is to be able to join the two different databases together and drive off of the master_item field for my report.

Accounting: Blue color
WMS: Green color

Here is my present code:
(Note: I need to use the "cast" command because the field lengths must match at both of 31 characters.)

select 
	case 
	when [COLOR=blue]itemnmbr[/color] is null 
		then [COLOR=green]cast(product as char (31))[/color] 
			else [COLOR=blue]itemnmbr[/color] 
				end as master_item,
				[COLOR=blue]itemnmbr[/color] as [COLOR=blue]GP_item[/color],
				[COLOR=green]cast(product as char(31))[/color] as [COLOR=green]RB_item[/color]

from [COLOR=blue]acntg.dbo.iv00101[/color] as [COLOR=blue]iv00101[/color] right outer join
	[COLOR=green]wms.dbo.prodmstr[/color] as [COLOR=green]prodmstr[/color] on 
		[COLOR=blue]iv00101.itemnmbr[/color] = [COLOR=green]prodmstr.product[/color]

Here are my desired results

[tt]
master_item GP_item RB_item
09283 09283 09283
10345 null 10345
21456 21456 21456
45333 45333 null
56444 56444 56444
[/tt]

We have an accounting system which has an item number file and a WMS which also has an item file. Logically if an item in in the accounting system it has to be in the wms and vice versa. The item is setup only in the accounting system and then gets downloaded to the wms. Because of whatever reason sometimes the item is in the accounting system but not in the wms and viceversa. This view is to let me know when this happens.

Presently with the code if the item does not exist in the accounting system (GP_item) master_item populates properly from RB_item (wms). But if the item is in accounting system (GP_Item) but not in RB-Item(wms) the entire record is missing.

I have altered the code so that it tests for the null on product instead of itemnmbr but I get the same results, just no record for an item in the accouting but not in wms.

Any help would be appreciated.

Thanks

 

[maswien]

You should be able to get the desired results by using FULL OUTER JOIN instead of the RIGHT OUTER JOIN.

 

[markajem]

Thanks that did the trick.