sql server - SQL Join two tables with different number of rows get all rows from one table -

i'm trying join 2 tables different number of rows. client number identifying field in both tables.

the first table includes client number clients. either referenced). complicate things further, clientnumber2 field text field need number other client number fields. there region field in second table need limit region numbers. second table includes client number clients meet specifications. of clients in second table have second client number.

i want create new column shows client number first table clients not exist in second table, shows second client number in second table if exists, otherwise, client number first table (which same client number in first column of second table

i've included syntax i'm using below. runs without errors. originalcn field returns desired value value in clientnumber2 of table 2 returning null values others. cannot figure out how work correctly. i've included sample tables , desired table. appreciated!

client table 1 client number 1 2 3 4 5 6 7 8  client table 2   client number   2nd client number     region 2                 14                    1 6                                       2 8                 15                    2  desired result 1 14 3 4 5 6 7 15 

here syntax using:

select table2.clientnumber table1.clientnumber  case when table2.clientnumber null table1.clientnumber  when table2.clientnumber2 null table2.clientnumber  when table2.clientnumber2 = ' ' table2.clientnumber  else cast(table2.clientnumber2 int)  end originalcn  dss.dbo.table1 left outer join rpto.dbo.table2 on dss.dbo.table1.clientnumber = rpto.dbo.table2.clientnumber  table2.region in (1,2,3) 

try this:

select coalesce(cast(nullif(t2.clientnumber2,' ') int), t1.clientnumber) clientnumber dss.dbo.table1 t1 left join rpto.dbo.table2 t2 on t1.clientnumber = t2.clientnumber coalesce(t2.region, 1) in (1,2,3)