SQL Server : Join Two Tables and Return With Null Records -

i couldn't figure out how join these 2 tables , result null value want. played around left join, right join, full outer join..., couldn't work. please see below.

table1:

nameid  name 1       n1 2       n2 3       n3 4       n4 5       n5 

table2:

nameid  anotherid   value 1       aid-111     1000 2       aid-222     2000 2       aid-222     3000 3       aid-333     4000 4       aid-444     5000   select ... join table1 , table2 anotherid = 'aid-222'  

this result want:

nameid  name    anotherid   value 1       n1      null        null 2       n2      aid-222     2000 3       n3      aid-222     3000     4       n4      null        null     5       n5      null        null 

please help. thanks!

you did not explain why want return value of 2000 instead of 3000 can use left join subquery result:

select t1.nameid,   t1.name,   t2.anotherid,   t2.value table1 t1 left join (   select nameid, anotherid, min(value) value   table2   anotherid = 'aid-222'   group nameid, anotherid ) t2   on t1.nameid = t2.nameid; 

see sql fiddle demo. gives result:

| nameid | name | anotherid |  value | -------------------------------------- |      1 |   n1 |    (null) | (null) | |      2 |   n2 |   aid-222 |   2000 | |      3 |   n3 |    (null) | (null) | |      4 |   n4 |    (null) | (null) | |      5 |   n5 |    (null) | (null) |