sql server - SQL Joins . One to many relationship -

i have 2 tables below

 table 1 ----------------------------------- userid   | username | age | salary ----------------------------------- 1        | foo      | 22  | 33000        ----------------------------------- 

 table 2 ------------------------------------------------ userid   | age      | salary     | createddate ------------------------------------------------ 1        | null     | 35000      | 2015-01-01 ------------------------------------------------ 1        |  28      | null       | 2015-02-01 ------------------------------------------------ 1        |  null    | 28000      | 2015-03-01 ------------------------------------------------ 

i need result this.

 result ----------------------------------- userid   | username | age | salary ----------------------------------- 1        | foo      | 28  | 28000 ----------------------------------- 

this example. in real project have around 6 columns age , salary in above tables.

in table 2 , each record have have 1 value i.e if age has value salary null , viceversa.

update :

table 2 has createddate column. want latest "notnull" cell value instead of maximum value.

note: i'm giving benefit of doubt know you're doing, , haven't told schema.

it looks table 2 "updates" table, in each row contains delta of changes apply base entity in table 1. in case can retrieve each column's data correlated join (technically outer-apply) , put results together. following:

select a.userid, a.username,      coalesce(aage.age, a.age),     coalesce(asalary.salary, a.salary) [table 1] outer apply (     select age     [table 2] x     x.userid = a.userid      , x.age not null     , not exists (         select 1         [table 2] y         x.userid = y.userid         , y.id > x.id         , y.age not null     ) ) aage, outer apply (     select salary     [table 2] x     x.userid = a.userid      , x.salary not null     , not exists (         select 1         [table 2] y         x.userid = y.userid         , y.id > x.id         , y.salary not null     ) ) asalary 

do note assuming have @ minimum id column in table 2 monotonically increasing each insert. if have "change time" column, use instead latest row, better.