T-SQL Left join twice

Question

Query below works as planned, it shows exactly the way i joined it, and that is fine, but problem with it, is that if you have more "specialization" tables for users, something like "Mail type" or anything that user can have more then one data ... you would have to go two left joins for each and "give priority" via ISNULL (in this case)

I am wondering, how could I avoid using two joins and "give" priority to TypeId 2 over TypeId 1 in a single join, is that even possible?

if object_id('tempdb..#Tab1') is not null drop table #Tab1
create table #Tab1 (UserId int, TypeId int)
if object_id('tempdb..#Tab2') is not null drop table #Tab2
create table #Tab2 (TypeId int, TypeDescription nvarchar(50))

insert into #Tab1 (UserId, TypeId)
values
(1, 1),
(1, 2)
insert into #Tab2 (TypeId, TypeDescription)
values
(1, 'User'),
(2, 'Admin')

select *, ISNULL(t2.TypeDescription, t3.TypeDescription) [Role]
from #Tab1 t1
    LEFT JOIN #Tab2 t2 on t1.TypeId = t2.TypeId and
                          t2.TypeId = 2
    LEFT JOIN #Tab2 t3 on t1.TypeId = t3.TypeId and
                          t3.TypeId = 1

Answer 1

The first problem is determining priority. In this case, you could use the largest TypeId, but that does not seem like a great idea. You could add another column to serve as a priority ordinal instead.

From there, it is a top 1 per group query:

using top with ties and row_number():

select top 1 with ties
    t1.UserId, t1.TypeId, t2.TypeDescription
from #Tab1 t1
  left join #Tab2 t2
    on t1.TypeId = t2.TypeId
order by row_number() over (
      partition by t1.UserId 
      order by t2.Ordinal
      --order by t1.TypeId desc
)

using common table expression and row_number():

;with cte as (
select t1.UserId, t1.TypeId, t2.TypeDescription
  , rn = row_number() over (
      partition by t1.UserId 
      order by t2.Ordinal
      --order by t1.TypeId desc
    )
from #Tab1 t1
  left join #Tab2 t2
    on t1.TypeId = t2.TypeId
)
select UserId, TypeId, TypeDescription
from cte
where rn = 1

---

rextester demo for both: http://rextester.com/KQAV36173

both return:

+--------+--------+-----------------+
| UserId | TypeId | TypeDescription |
+--------+--------+-----------------+
|      1 |      2 | Admin           |
+--------+--------+-----------------+