sql server - SQL JOIN with COALESCE in condition is duplicating rows -

i trying join 2 tables together. first table contains data records not want duplicate. second table joining first table lookup [value] distinct [profileid] , [role]. [profileid], [role] column in second table has unique constraint on combination, [role] can null, in case treat value default profile.

how can join these tables without duplicating rows, , without using multiple left joins? actual query more complex example.

see example below.

declare @temp table ([profileid] int, [role] int) declare @temp2 table ([profileid] int, [role] int, [value] nvarchar(50))  insert @temp ([profileid], [role]) values (1, 1) insert @temp ([profileid], [role]) values (1, 2) insert @temp ([profileid], [role]) values (2, 1) insert @temp ([profileid], [role]) values (2, 2) insert @temp2 ([profileid], [role], [value]) values (1, 1, 'match') insert @temp2 ([profileid], [role], [value]) values (1, null, 'default1') insert @temp2 ([profileid], [role], [value]) values (2, null, 'default2')  select     t1.[profileid],     t1.[role],     t2.value     @temp t1     join @temp2 t2 on t1.profileid = t2.profileid , coalesce(t2.[role], t1.[role]) = t1.[role] 

this gives me (and understand why)

================================ | profileid | role  |  value   | ================================ |     1     |   1   |  match   | -------------------------------- |     1     |   1   | default1 | -------------------------------- |     1     |   2   | default1 | -------------------------------- |     2     |   1   | default2 | -------------------------------- |     2     |   2   | default2 | ================================ 

while want

================================ | profileid | role  |  value   | ================================ |     1     |   1   |  match   | -------------------------------- |     1     |   2   | default1 | -------------------------------- |     2     |   1   | default2 | -------------------------------- |     2     |   2   | default2 | ================================ 

you can use apply , top:

select     t.profileid,     t.role,     x.value @temp t outer apply(     select top 1 value     @temp2             profileid = t.profileid         , (role = t.role or role null)     order          case when role not null 0 else 1 end )x